How do I import an EXCEL FILE WITH MULTIPLE WORKSHEETS into Access 2003. I am
creating a master table in Access of archived Excel files, each file has
multiple worksheets. Is there a need to use both SQL and VBA to accomplished
the import. TIA

Do you know the names of the worksheets in the files? Or do you have to
"read" the worksheet names from the EXCEL files?

If the former is true, you can use the code at this web page as a starting
point. Just add another loop or extra steps for the various worksheets.
Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXC...ImpFolderFiles
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"KramerJ" <(E-Mail Removed)> wrote in message
news:6E58A88E-0A62-42FA-9E32-(E-Mail Removed)...
> How do I import an EXCEL FILE WITH MULTIPLE WORKSHEETS into Access 2003. I
> am
> creating a master table in Access of archived Excel files, each file has
> multiple worksheets. Is there a need to use both SQL and VBA to
> accomplished
> the import. TIA

I tried to do this a few days ago; never figured it out. How do you "read"
the worksheet names from the Excel files?

I tried this, but it didn't work:
For i = 1 To Workbooks(MyWorkBook).Sheets.Count
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTablename, strPath & strFile, blnHasFieldNames
Next i

Here is the code that I was working with (this imported the first sheet in
each Excel file):
Sub ImportAllExcelFiles()
On Error GoTo Err_F
Dim strPathFile As String, strFile As String, strPath As String, strSpec As
String
Dim strTable As String, ynFieldName As Boolean
ynFieldName = False
strPath = "C:\Import\"
'strSpec = "NameOfImportSpecification" ' Put your name here
strTable = "tablename"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
strPathFile, ynFieldName
' Uncomment out the next code step if you want to delete the file after it's
imported
' Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub

Again, it reads the first sheet, and only the first sheet, in each Excel file.
How can this be modified to read multiple sheets in each Excel file, and
also loop through all the Excel files in a folder?

Thanks,
Ryan---
--
RyGuy--
If the post was helpful, please click the ''Yes'' button to indicate such!


"Ken Snell MVP" wrote:

> Do you know the names of the worksheets in the files? Or do you have to
> "read" the worksheet names from the EXCEL files?
>
> If the former is true, you can use the code at this web page as a starting
> point. Just add another loop or extra steps for the various worksheets.
> Import Data from All EXCEL Files in a single Folder via TransferSpreadsheet
> http://www.accessmvp.com/KDSnell/EXC...ImpFolderFiles
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "KramerJ" <(E-Mail Removed)> wrote in message
> news:6E58A88E-0A62-42FA-9E32-(E-Mail Removed)...
> > How do I import an EXCEL FILE WITH MULTIPLE WORKSHEETS into Access 2003. I
> > am
> > creating a master table in Access of archived Excel files, each file has
> > multiple worksheets. Is there a need to use both SQL and VBA to
> > accomplished
> > the import. TIA
>
>
>

Reading the worksheet names from the EXCEL file involves opening the EXCEL
file, storing the worksheet names that the code reads as it loops through
the worksheets, closing the EXCEL file, then putting a loop around your
import code for the list of worksheet names.

I've got some code to do this, which I wrote a while back.... let me find
it, and I'll post an example on my website. I've been meaning to do this for
a while, and I think I've got some time tonite to do that...so, I will!

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"ryguy7272" <(E-Mail Removed)> wrote in message
news80BF75C-C020-44F1-AAE1-(E-Mail Removed)...
>I tried to do this a few days ago; never figured it out. How do you "read"
> the worksheet names from the Excel files?
>
> I tried this, but it didn't work:
> For i = 1 To Workbooks(MyWorkBook).Sheets.Count
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
> strTablename, strPath & strFile, blnHasFieldNames
> Next i
>
> Here is the code that I was working with (this imported the first sheet in
> each Excel file):
> Sub ImportAllExcelFiles()
> On Error GoTo Err_F
> Dim strPathFile As String, strFile As String, strPath As String, strSpec
> As
> String
> Dim strTable As String, ynFieldName As Boolean
> ynFieldName = False
> strPath = "C:\Import\"
> 'strSpec = "NameOfImportSpecification" ' Put your name here
> strTable = "tablename"
> strFile = Dir(strPath & "*.xls")
> Do While Len(strFile) > 0
> strPathFile = strPath & strFile
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
> strPathFile, ynFieldName
> ' Uncomment out the next code step if you want to delete the file after
> it's
> imported
> ' Kill strPathFile
> strFile = Dir()
> Loop
> Exit_F:
> Exit Sub
>
> Err_F:
> MsgBox Err.Number & " " & Err.Description
> Resume Exit_F
>
> End Sub
>
> Again, it reads the first sheet, and only the first sheet, in each Excel
> file.
> How can this be modified to read multiple sheets in each Excel file, and
> also loop through all the Excel files in a folder?
>
> Thanks,
> Ryan---
> --
> RyGuy--
> If the post was helpful, please click the ''Yes'' button to indicate such!
>
>
> "Ken Snell MVP" wrote:
>
>> Do you know the names of the worksheets in the files? Or do you have to
>> "read" the worksheet names from the EXCEL files?
>>
>> If the former is true, you can use the code at this web page as a
>> starting
>> point. Just add another loop or extra steps for the various worksheets.
>> Import Data from All EXCEL Files in a single Folder via
>> TransferSpreadsheet
>> http://www.accessmvp.com/KDSnell/EXC...ImpFolderFiles
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>> http://www.accessmvp.com/KDSnell/
>>
>>
>> "KramerJ" <(E-Mail Removed)> wrote in message
>> news:6E58A88E-0A62-42FA-9E32-(E-Mail Removed)...
>> > How do I import an EXCEL FILE WITH MULTIPLE WORKSHEETS into Access
>> > 2003. I
>> > am
>> > creating a master table in Access of archived Excel files, each file
>> > has
>> > multiple worksheets. Is there a need to use both SQL and VBA to
>> > accomplished
>> > the import. TIA
>>
>>
>>

OK here it is:

Import Data from All Worksheets in a single EXCEL File via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXC...m#ImpAllWkshts

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"ryguy7272" <(E-Mail Removed)> wrote in message
news80BF75C-C020-44F1-AAE1-(E-Mail Removed)...
>I tried to do this a few days ago; never figured it out. How do you "read"
> the worksheet names from the Excel files?
>
> I tried this, but it didn't work:
> For i = 1 To Workbooks(MyWorkBook).Sheets.Count
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
> strTablename, strPath & strFile, blnHasFieldNames
> Next i
>
> Here is the code that I was working with (this imported the first sheet in
> each Excel file):
> Sub ImportAllExcelFiles()
> On Error GoTo Err_F
> Dim strPathFile As String, strFile As String, strPath As String, strSpec
> As
> String
> Dim strTable As String, ynFieldName As Boolean
> ynFieldName = False
> strPath = "C:\Import\"
> 'strSpec = "NameOfImportSpecification" ' Put your name here
> strTable = "tablename"
> strFile = Dir(strPath & "*.xls")
> Do While Len(strFile) > 0
> strPathFile = strPath & strFile
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
> strPathFile, ynFieldName
> ' Uncomment out the next code step if you want to delete the file after
> it's
> imported
> ' Kill strPathFile
> strFile = Dir()
> Loop
> Exit_F:
> Exit Sub
>
> Err_F:
> MsgBox Err.Number & " " & Err.Description
> Resume Exit_F
>
> End Sub
>
> Again, it reads the first sheet, and only the first sheet, in each Excel
> file.
> How can this be modified to read multiple sheets in each Excel file, and
> also loop through all the Excel files in a folder?
>
> Thanks,
> Ryan---
> --
> RyGuy--
> If the post was helpful, please click the ''Yes'' button to indicate such!
>
>
> "Ken Snell MVP" wrote:
>
>> Do you know the names of the worksheets in the files? Or do you have to
>> "read" the worksheet names from the EXCEL files?
>>
>> If the former is true, you can use the code at this web page as a
>> starting
>> point. Just add another loop or extra steps for the various worksheets.
>> Import Data from All EXCEL Files in a single Folder via
>> TransferSpreadsheet
>> http://www.accessmvp.com/KDSnell/EXC...ImpFolderFiles
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>> http://www.accessmvp.com/KDSnell/
>>
>>
>> "KramerJ" <(E-Mail Removed)> wrote in message
>> news:6E58A88E-0A62-42FA-9E32-(E-Mail Removed)...
>> > How do I import an EXCEL FILE WITH MULTIPLE WORKSHEETS into Access
>> > 2003. I
>> > am
>> > creating a master table in Access of archived Excel files, each file
>> > has
>> > multiple worksheets. Is there a need to use both SQL and VBA to
>> > accomplished
>> > the import. TIA
>>
>>
>>

Slight change in title, and an additional example:

Import Data from All Worksheets in a single EXCEL File into One Table via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXC...m#ImpAllWkshts


Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXC...pAllWktsSepTbl

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Ken Snell MVP" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> OK here it is:
>
> Import Data from All Worksheets in a single EXCEL File via
> TransferSpreadsheet
> http://www.accessmvp.com/KDSnell/EXC...m#ImpAllWkshts
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news80BF75C-C020-44F1-AAE1-(E-Mail Removed)...
>>I tried to do this a few days ago; never figured it out. How do you
>>"read"
>> the worksheet names from the Excel files?
>>
>> I tried this, but it didn't work:
>> For i = 1 To Workbooks(MyWorkBook).Sheets.Count
>> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
>> strTablename, strPath & strFile, blnHasFieldNames
>> Next i
>>
>> Here is the code that I was working with (this imported the first sheet
>> in
>> each Excel file):
>> Sub ImportAllExcelFiles()
>> On Error GoTo Err_F
>> Dim strPathFile As String, strFile As String, strPath As String, strSpec
>> As
>> String
>> Dim strTable As String, ynFieldName As Boolean
>> ynFieldName = False
>> strPath = "C:\Import\"
>> 'strSpec = "NameOfImportSpecification" ' Put your name here
>> strTable = "tablename"
>> strFile = Dir(strPath & "*.xls")
>> Do While Len(strFile) > 0
>> strPathFile = strPath & strFile
>> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
>> strPathFile, ynFieldName
>> ' Uncomment out the next code step if you want to delete the file after
>> it's
>> imported
>> ' Kill strPathFile
>> strFile = Dir()
>> Loop
>> Exit_F:
>> Exit Sub
>>
>> Err_F:
>> MsgBox Err.Number & " " & Err.Description
>> Resume Exit_F
>>
>> End Sub
>>
>> Again, it reads the first sheet, and only the first sheet, in each Excel
>> file.
>> How can this be modified to read multiple sheets in each Excel file, and
>> also loop through all the Excel files in a folder?
>>
>> Thanks,
>> Ryan---
>> --
>> RyGuy--
>> If the post was helpful, please click the ''Yes'' button to indicate
>> such!
>>
>>
>> "Ken Snell MVP" wrote:
>>
>>> Do you know the names of the worksheets in the files? Or do you have to
>>> "read" the worksheet names from the EXCEL files?
>>>
>>> If the former is true, you can use the code at this web page as a
>>> starting
>>> point. Just add another loop or extra steps for the various worksheets.
>>> Import Data from All EXCEL Files in a single Folder via
>>> TransferSpreadsheet
>>> http://www.accessmvp.com/KDSnell/EXC...ImpFolderFiles
>>> --
>>>
>>> Ken Snell
>>> <MS ACCESS MVP>
>>> http://www.accessmvp.com/KDSnell/
>>>
>>>
>>> "KramerJ" <(E-Mail Removed)> wrote in message
>>> news:6E58A88E-0A62-42FA-9E32-(E-Mail Removed)...
>>> > How do I import an EXCEL FILE WITH MULTIPLE WORKSHEETS into Access
>>> > 2003. I
>>> > am
>>> > creating a master table in Access of archived Excel files, each file
>>> > has
>>> > multiple worksheets. Is there a need to use both SQL and VBA to
>>> > accomplished
>>> > the import. TIA
>>>
>>>
>>>
>
>

Thanks for the code Ken! Amazing, just amazing! I looked at your site a few
times before, but didn’t see that code there. Is it a recent addition to
your site?

You may, or may not, know about this link:
http://www.rondebruin.nl/copy2.htm

That is a great way to get data from all Excel sheets, in one workbook, into
a single sheet, which, then, is quite easy to import into Access. So,
anyway, just wanted to share with you, and others out there reading this.

Thanks again!!
Ryan---

That code is new to the site; put it up there last night. So it's no
surprise that you hadn't seen it before < g >.

Thanks for link to Ron's site.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"ryguy7272" <(E-Mail Removed)> wrote in message
news:04E78DB2-8110-4539-95D6-(E-Mail Removed)...
> Thanks for the code Ken! Amazing, just amazing! I looked at your site a
> few
> times before, but didn't see that code there. Is it a recent addition to
> your site?
>
> You may, or may not, know about this link:
> http://www.rondebruin.nl/copy2.htm
>
> That is a great way to get data from all Excel sheets, in one workbook,
> into
> a single sheet, which, then, is quite easy to import into Access. So,
> anyway, just wanted to share with you, and others out there reading this.
>
> Thanks again!!
> Ryan---
>

[prashantpatole]

If you dont want to do this using any kind of program (manually).

Import the Excel into a SQL server blank database and then Export it to MS Acess.

You can do this in SQL Management studio. Right click on blank database-> select Task, and select Imort/Export.