import excel multiple sheets 2

[mrMika]

I'm looking for an efficient way to import data from an excel spread sheet that has multiple sheets. Any ideas?

 

[judgehopkins]

http://www.experts-exchange.com/Databases/MS_Access/Q_20064021.html#5825017

Also, use the Wizard.

Right-click anywhere in the database window.

Choose Import (or File/Get External Data/Import).

From the import dialog box, select Excel from the files of Type drop-down list.

Select the file you want and click import.

Since you are selecting Excel, the Import Spreadsheet Wizard appears. Follow the prompts.

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[judgehopkins]

thread702-578563

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[BillPower]

Hi Judge,

Thanks for pointing mrMika to my thread. Unfortuneately, the demo currently on my "site" only caters for importing 1 sheet at a time.

I am working on a multiple sheet version. Will let you both know when I've got it up and running.

Bill

 

[mrMika]

Thank you for your suggestions. The problem with this as pointed out by billPower is multiple sheets. Does anyone have any code for going through the sheets and doing and import??

 

[mrMika]

Just to let you know that I got it working. In case you are interested I used this code to loop through the worksheets

'add loop to move through worksheets
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlDay As String
Dim i As Integer


Set xlApp = CreateObject("Excel.Application&quot
Set xlBook = xlApp.Workbooks.Open(strExcel, , , , "Skiing9&quot
Set xlSheet = xlBook.ActiveSheet


For i = 1 To xlBook.Worksheets.Count



xlBook.Worksheets(i).Select


xlDay = Worksheets(i).Name

Dim tst As String

Set xlSheet = xlBook.Worksheets(i)

DoCmd.TransferSpreadsheet acImport, , strSaveAs & xlDay, strExcel, , xlDay & "!"
Next i


Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

 

[BeachBumTechie]

How about the REVERSE of this PROCESS (i.e. OUTPUT 2 different ACCESS TABLES to a SINGLE EXCEL FILE with multiple worksheets).

Any help would be greatly appreciated.

 

[BillPower]

Hi BeachBumTechie,

Most things are possible with Access, if you give a bit more detail I'll see what I can do.

BTW MrMika and Judge,

I posted a Excel MultiImport demo at

http://www.wpower.fsbusiness.co.uk/

the file to look at is Import Multiple Excel Worksheets into Access

Didn't get much of a response on this one though, don't know why.

Bill

 

[mrMika]

Thanks BillPower for your input. I had actually used your earlier version of importing from a dialogue box (having stumbled upon it by accident) and made modifications to it. I also did check out your latest version and borrowed a couple of ideas from it as well. Both of these helped me significantly and I appreciate your help on this.

BeachBumTechie...did you get your problem resolved?