Newbie... Macro - Importing multiple csv files to excel

dugong

New Member
Joined
Oct 20, 2006
Messages
36
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

dugong

New Member
Joined
Oct 20, 2006
Messages
36
Office Version
  1. 365
Platform
  1. MacOS
Does anyone have any idea on how I could do this?

Dugong
 

Forum statistics

Threads
1,136,368
Messages
5,675,358
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top