Import multiple text files into one Excel workbook

[MCSGraham]

I'm exporting 10 tab-delimited text files from an Access db and trying to get these 10 files auto imported into one Excel workbook -- each file on a different sheet.

I've tried doing this a couple different ways -- using some ADO recordset code online that didn't work and then trying the "objExcel.Workbooks.OpenText" method.

When I use the following code my first file is added and saved fine:

Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText "E:\DUMP\HEALTH.txt"
objExcel.SaveWorkspace ("E:\DUMP\TEST.xls")

I cannot use the "OpenText" method again though or it will create a new workbook each time it's used.

I've tried to add another worksheet, make it active and then do the "OpenText" method again but doesn't work.

Does anyone know the best way to import text files into one Excel file but on seperate sheets?

thanks a lot in advance for any help!

 

[BoxHead]

Take a look at thread701-1198915

This uses the recordset method, but you didn't specify what didn't work so it may be the route to go.


HTH



John






When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[MCSGraham]

The thread you listed went to an issue with exporting from Access.

What was the thread dealing with the recordset method of importing text files into Excel on separate sheets?

 

[BoxHead]

I'm exporting 10 tab-delimited text files from an Access db and trying to get these 10 files auto imported into one Excel workbook -- each file on a different sheet.

The thread I pointed you to exports information from Access to Excel. Or, to say it the other way, Imports to Excel from Access.

As the Recordset is looped through, the SQL for the TransferSpreadsheet method is revised (in that example, for each Vendor). With each revision to the SQL and each change of the Query name, a new tab is added to the Excel workbook.


HTH


John




When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.