Results 1 to 5 of 5

Thread: batch import

  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I am looking for a way to batch import text (tab delimited or comma delimited files).

    What I would like to do is import 20-30 files into a single workbook, with each file being imported into a separate worksheet and the worksheet being given the filename. What I was thinking of is having a macro read a batch file which contains the names of the data files. Each file would be imported into a similarly named worksheet. I'd then have a master worksheet to manipulate the data. Is there a way to do that, either by having a master workbook template into which everything would be imported, or by creating a new workbook each time?

    Thanks.

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jepalmer' post='793036' date='11-Sep-2009 15:38']I am looking for a way to batch import text (tab delimited or comma delimited files).

    What I would like to do is import 20-30 files into a single workbook, with each file being imported into a separate worksheet and the worksheet being given the filename. What I was thinking of is having a macro read a batch file which contains the names of the data files. Each file would be imported into a similarly named worksheet. I'd then have a master worksheet to manipulate the data. Is there a way to do that, either by having a master workbook template into which everything would be imported, or by creating a new workbook each time?

    Thanks.[/quote]
    Will all of the data files reside in one folder?
    Will any non-data files reside in that folder?
    Regards
    Don

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jepalmer' post='793036' date='11-Sep-2009 15:38']I am looking for a way to batch import text (tab delimited or comma delimited files).

    What I would like to do is import 20-30 files into a single workbook, with each file being imported into a separate worksheet and the worksheet being given the filename. What I was thinking of is having a macro read a batch file which contains the names of the data files. Each file would be imported into a similarly named worksheet. I'd then have a master worksheet to manipulate the data. Is there a way to do that, either by having a master workbook template into which everything would be imported, or by creating a new workbook each time?

    Thanks.[/quote]

    The following code imports csv files immediately after the sheet named "Master".
    Code:
    Sub CaptureCSV(pathspec As String, fn As String)
     '
     '
    	 ChDir pathspec
    	 Workbooks.Open filename:=fn & ".csv"
    	 Sheets(fn).Copy After:=ThisWorkbook.Sheets("Master")
    	 Windows(fn & ".csv").Close
     End Sub
     Sub test()
     Dim pathspec As String
     Dim filename As String
     
    	 pathspec = "C:\Documents and Settings\W. Donald Wells\Desktop"
    	 filename = "pbook"
    	 
    	 Call CaptureCSV(pathspec, filename)
    	 
     End Sub
    Regards
    Don

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='793038' date='11-Sep-2009 20:48']Will all of the data files reside in one folder?
    Will any non-data files reside in that folder?[/quote]

    It can be set up in any way, but all of the data files would reside in a single folder and it would be simpler from both a programming standpoint and perhaps a user standpoint to have only the data files in that folder.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='793045' date='11-Sep-2009 21:31']The following code imports csv files immediately after the sheet named "Master".
    Code:
    Sub CaptureCSV(pathspec As String, fn As String)
     '
     '
    	 ChDir pathspec
    	 Workbooks.Open filename:=fn & ".csv"
    	 Sheets(fn).Copy After:=ThisWorkbook.Sheets("Master")
    	 Windows(fn & ".csv").Close
     End Sub
     Sub test()
     Dim pathspec As String
     Dim filename As String
     
    	 pathspec = "C:\Documents and Settings\W. Donald Wells\Desktop"
    	 filename = "pbook"
    	 
    	 Call CaptureCSV(pathspec, filename)
    	 
     End Sub
    [/quote]

    Thanks for the help and I'll be looking at this over the next couple of weeks (it's a low priority at the moment).

Posting Permissions

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