MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help editing a Macro

  Asked By: Carole    Date: Aug 31    Category: MS Office    Views: 939

User should be able to enter a stock symbol in B1 and the data is
imported from directory: H:\NYSEDAT\ in the format symbol.txt. There
are approximately 3000 files in that directory.

Prior to recording this macro, I entered "Stock Symbol" in cell A1
and stuck the symbol "GE" in cell B1 as a sample.

I recorded the macro using DATA> IMPORT EXTERNAL DATA > IMPORT DATA
and went through the steps required to import the ge.txt file to
sheet1 of getcsvdata.xls beginning with cells A2:F2 with contains
header information as follows:
Date Open High Low Close Volume
data starts at A3:F3 and goes down the sheet to A255:F255

The problem I'm having is figuring out how to edit the macro to
import the data for whatever stock symbol the user enters in cell B1.

Here is the macro I recorded below:

Sub getcsvdata()
' getcsvdata Macro
With ActiveSheet.QueryTables.Add
(Connection:="TEXT;H:\NYSEDAT\GE.txt", _
.Name = "GE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "mm/dd/yy;@"
End Sub



1 Answer Found

Answer #1    Answered By: Neil Turner     Answered On: Aug 31

Change the line near the top which reads
.Name = "GE"
.Name = Range("B1")
.Name = Range("B1") & ".txt"

Didn't find what you were looking for? Find more on Help editing a Macro Or get search suggestion and latest updates.