Hello
I am new to the board and very new to macros/advanced formulas! .. And I am hoping to get some help/guidance!
I am trying to automate a monthly process of importing, into an excel file with links to charts/graphs/lookups, 9 separate csv files – selecting specific ranges from each csv file (i.e.: always from cell B8 to the last used cell). The csv files are to be imported a specific way (i.e.: Windows ANSI and text format) as I need to deal with special characters/foreign accents.
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\My Documents\My Data Sources\UK.csv" _
, Destination:=Range("A1"))
.Name = "UK"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\My Documents\My Data Sources\Germany Only.csv" _......
I tried setting up the macro by recording the imports but when I look at the code, it looks like I would need to amend the file locations?! However I would like it to go directly to a filename in the same directory and would like it to automatically go to the next page.
In other words, the generated csv files and the excel input file would be in a certain directory. I would then like to open the excel file and import the csv files into the relevant sheet (according to their name) for the specific range (i.e.: desired range from csv file ‘UK.csv’ imported into sheet ‘UK’, then 'Germany.csv' to 'Germany', etc for the 9 files).
The filenames will remain the same every time but the directory will change each month (i.e. the excel file would be copied to a new directory and the macros re-run to import and update).
Can anyone assist? Any help much appreciated.
Dugong.
I am new to the board and very new to macros/advanced formulas! .. And I am hoping to get some help/guidance!
I am trying to automate a monthly process of importing, into an excel file with links to charts/graphs/lookups, 9 separate csv files – selecting specific ranges from each csv file (i.e.: always from cell B8 to the last used cell). The csv files are to be imported a specific way (i.e.: Windows ANSI and text format) as I need to deal with special characters/foreign accents.
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\My Documents\My Data Sources\UK.csv" _
, Destination:=Range("A1"))
.Name = "UK"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\My Documents\My Data Sources\Germany Only.csv" _......
I tried setting up the macro by recording the imports but when I look at the code, it looks like I would need to amend the file locations?! However I would like it to go directly to a filename in the same directory and would like it to automatically go to the next page.
In other words, the generated csv files and the excel input file would be in a certain directory. I would then like to open the excel file and import the csv files into the relevant sheet (according to their name) for the specific range (i.e.: desired range from csv file ‘UK.csv’ imported into sheet ‘UK’, then 'Germany.csv' to 'Germany', etc for the 9 files).
The filenames will remain the same every time but the directory will change each month (i.e. the excel file would be copied to a new directory and the macros re-run to import and update).
Can anyone assist? Any help much appreciated.
Dugong.
