Macro to import CSV file from fixed directory

[Anne]

Hello! I am in the process of developing a quality control spreadsheet based
on CSV files that are exported periodically from a scientific instrument. The
CSV file always goes to the same place on the hard drive. For example,
C:\ICAP\QAQC

I need help on creating a macro button in the Excel 2007 workbook I'm
working on to automatically go to that directory and pull in any CSV file it
finds there. Once it imports the CSV as a sheet in the workbook, then I can
tell it to add the data from the sheet onto a master sheet for charting
purposes. But I do need some help creating a macro button to automatically
pull the CSV in.

Any ideas would be appreciated! Thanks!

 

[slarbie]

If you just record the add sheet and import steps as you perform them, you
get something pretty usable. I just did so with a little sample csv file I
created with the following results. (macro recorder is your FRIEND!)

Sub Macro1()

Sheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\Data\MyData.csv", _
Destination:=Range("$A$1"))
.Name = "MyData"
.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(2, 1, 3)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub