+ Reply to Thread
Results 1 to 5 of 5

Import Data File Macro

  1. #1
    Registered User
    Join Date
    02-25-2010
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Import Data File Macro

    Hi,

    I'm trying to setup a macro to import a series of data files.

    I currently have:

    Sheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\...\4cm 0_9Hz 1o.dat" _
    , Destination:=Range("$A$1"))
    .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 = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = ":"
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

    NewName = Range("B4").Value
    ActiveSheet.Name = NewName

    End With

    However, I would like to be able to define the file locations before, say:

    Filename(1) = C:\...\4cm 0_9Hz 1o.dat
    Filename (2) = C:\...\4cm 0_9Hz 1o.dat

    etc.

    And then use a for loop to churn out all of the worksheets. I tried:

    Filename(1) = C:\...\4cm 0_9Hz 1o.dat

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;Filename(1)" _
    , Destination:=Range("$A$1"))

    But just got an error. I assume the term "TEXT;Filename(1)" is incorrect. I've tried removing TEXT etc, but my knowledge of code is none-existant and I am working on purely trial and error basis. Could anybody tell me what I am doing wrong?

    Thank you in advance,

    AS

  2. #2
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    762

    Re: Import Data File Macro

    Hi
    Save the attached file inside the folder with .dat files and run the macro. It lists all .dat files in col A and B and data in col C
    Ravi.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-25-2010
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Thumbs up Re: Import Data File Macro

    This is excellent. Thank you. However, could this be adjusted so that the data from file1.dat appears in worksheet "file1", and the data from file2.dat appears in worksheet "file2" and so on?

    I just tried to do this myself, but typically got in a pickle!

    Once again, thank you,

    AS

  4. #4
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    762

    Re: Import Data File Macro

    Hi
    try the modified codes in the attached file
    ravi
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-25-2010
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Import Data File Macro

    Perfect. Thank you.

+ 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