+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    10-23-2006
    Posts
    79

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

    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

  2. #2
    Forum Contributor
    Join Date
    02-23-2005
    Location
    England
    Posts
    110
    From what you say, I believe that all you need to do is remove the path from each filename and then Excel should look for the files in the same directory as the one the main file is opened from. Worth trying, anyway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1