+ Reply to Thread
Results 1 to 11 of 11

Reference Text File To Import Using Generic Letter or Number

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Reference Text File To Import Using Generic Letter or Number

    Hi all,

    I have recorded some script in excel to import a *.dat file, delete the first 19 rows of data, then export the data as a *.csv file.

    My problem is I have circa 30 *.dat files to import, all of which have large file names and I am fed up with copying and pasting them in.

    So my question is, in the below code, can I somehow use a reference at the start such as:

    abc1.dat = "1"
    abc2.dat = "2"

    Then where the files were used, reference the "1".dat and its exported version "1".csv for the abc1.dat case.

    Sub Import_dat_Modify_Export_csv()
    '
    '
    ' Macro to save *.dat files in *.csv format while removing the un-needed data at the top of the *.dat files
      '1
      With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;D:\TEMP\DIR\abc1.dat" _
            , Destination:=Range("A1"))
            .Name = "abc1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        Rows("1:19").Select
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        ChDir "D:\TEMP\X123"
        ActiveWorkbook.SaveAs Filename:= _
            "D:\TEMP\X123\abc1.csv" _
            , FileFormat:=xlCSV, CreateBackup:=False
        ActiveWindow.Close
        Workbooks.Add
     
      '2
      With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;D:\TEMP\DIR\abc2.dat" _
            , Destination:=Range("A1"))
            .Name = "abc2"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        Rows("1:19").Select
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        ChDir "D:\TEMP\X123"
        ActiveWorkbook.SaveAs Filename:= _
            "D:\TEMP\X123\abc2.csv" _
            , FileFormat:=xlCSV, CreateBackup:=False
        ActiveWindow.Close
        Workbooks.Add
     
    End Sub
    Appologies as I am somewhat *poor* at VBA code - hence my use of the Recorder.

    Cheers
    Last edited by VBA Noob; 01-26-2009 at 05:59 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello R_S_6,

    This version of your macro allows you to insert the file names into an array. Here the names are hard coded into the macro. The file names could be read from a sheet in the macro is running in. I would need to know what the sheet name is and the available column to have the macro read the file names in automatically.
    Sub Import_dat_Modify_Export_csv()
    '
    '
    ' Macro to save *.dat files in *.csv format while removing the un-needed data at the top of the *.dat files
      '1
      
      Dim FileName As String
      Dim Files(30) As String
      Dim I As Long
          
          Files(0) = "ABC1.dat"
          Files(1) = "ABC2.dat"
          Files(3) = "ABC3.dat"
         
            Do While Files(I) <> ""
              FileName = Split(File(I), ".")(0)
              With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;D:\TEMP\DIR\" & Files(I) _
                , Destination:=Range("A1"))
                .Name = FileName
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 850
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = True
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = True
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
              End With
              Rows("1:19").Select
              Selection.Delete Shift:=xlUp
              Range("A1").Select
              ChDir "D:\TEMP\X123"
              ActiveWorkbook.SaveAs FileName:= _
                  "D:\TEMP\X123\" & FileName & ".csv" _
                  , FileFormat:=xlCSV, CreateBackup:=False
              ActiveWindow.Close
              Workbooks.Add
              I = I + 1
              QueryTables(1).Delete
            Loop
            
    End Sub
    Sincerely,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    Hi Leith, thanks for your reply.

    If I were to call the sheet in which the file names are stored 'File_Names' and the file names were listed in Column A starting at Row 1...

    Can you show me the required alterations to your piece of code?

    Thanks Again!

    Cheers
    Last edited by R_S_6; 01-26-2009 at 04:46 AM.

  4. #4
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    Just had a go with the code you provided. (Although I prefer your idea of the file names in a sheet!)

    I had to modify this line:

    FileName = Split(File(I), ".")(0)
    to:
    FileName = Split(Files(I), ".")(0)
    As it came up with an error here (Compile Error).

    After that it hit the following error at line:
    QueryTables(1).Delete
    Saying 'Compile Error: Sub or Function not defined' Not a clue on this one!

    Thought it would be good to highlight the above before hopefully you manage to alter the code to read in the file names from 'Column A' of Sheet 'File_Names'.

    Thanks Again!

  5. #5
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Question Re: Reference Text File To Import Using Generic Letter or Number

    Bump - Any Ideas

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Reference Text File To Import Using Generic Letter or Number

    Hello R_S_6,

    I have made the changes to the macro. Thanks for catching the typo. My typing skills are not very good. The file names are now read from the worksheet named "File_Names" column "A" starting with row 1 down to the last entry in the column.
    Sub Import_dat_Modify_Export_csv()
    '
    '
    ' Macro to save *.dat files in *.csv format while removing the un-needed data at the top of the *.dat files
      '1
      
      Dim Cell As Range
      Dim FileName As String
      Dim Rng As Range
      Dim Wks As Worksheet
          
          
          Set Wks = Worksheets("File_Names")
          StartCell = "A1"
          
          With Wks
            C = .Range(StartCell).Column
            Set Rng = .Range(StartCell, .Cells(Rows.Count, C).End(xlUp))
          End With
         
            For Each Cell In Rng
              FileName = Split(Cell, ".")(0)
              With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;D:\TEMP\DIR\" & FileName _
                , Destination:=Range("A1"))
                .Name = FileName
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 850
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = True
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = True
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
              End With
              Rows("1:19").Select
              Selection.Delete Shift:=xlUp
              Range("A1").Select
              ChDir "D:\TEMP\X123"
              ActiveWorkbook.SaveAs FileName:= _
                  "D:\TEMP\X123\" & FileName & ".csv" _
                  , FileFormat:=xlCSV, CreateBackup:=False
              ActiveWindow.Close
              Workbooks.Add
              I = I + 1
            Next Cell
            
    End Sub
    Sincerely,
    Leith Ross

+ 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