1 Replies - 1944 Views - Last Post: 16 August 2010 - 06:36 PM Rate Topic: -----

#1 guyfromri   User is offline

  • D.I.C Addict

Reputation: 55
  • View blog
  • Posts: 838
  • Joined: 16-September 09

Importing a workbook with multiple worksheets to access

Posted 16 August 2010 - 08:13 AM

So I have an excel workbook with multiple worksheets. I am trying to open it and import to access. I'm not quite sure how to do this. Would I open the workbook as an object? If so I don't really know where to go from there. Or is there another way I can do this? I don't really have any code yet because I'm not quite sure where to start. I googled it but it came back with a lot of SQL stuff and that's not really the direction I want to take with this. If anyone has any good reading material, I would really appreciate it! Thanks!

I'm thinking something like this for now
dim oxl as object
dim wb as object
dim shtcount as integer
dim shts as integer

set oxl=getobject(,"Excel.Application")
set wb=oxl.openworkbook("myWorkbook.xls")
shtcount=wb.sheets.count 'This number could change each time I receive a new file from a client

for shts=1 to shtcount
'So what would I do here? Normally I would do --
docmd.transfer spreadsheet
'But will that method work on an open workbook and import just one sheet at a time?
next



'As always, thanks in advance!!!

This post has been edited by guyfromri: 16 August 2010 - 08:14 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Importing a workbook with multiple worksheets to access

#2 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Importing a workbook with multiple worksheets to access

Posted 16 August 2010 - 06:36 PM

Public Sub GetExcel()

Dim oXL As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set oXL = CreateObject("Excel.Application")
Set wb = oXL.Workbooks.Open("C:\Users\June\Test.xlsx")

For Each ws In wb.Worksheets
    DoCmd.TransferSpreadsheet acImport, , ws.Name, "C:\Users\June\Test.xlsx", True, ws.Name & "!A1:B3"
Next

wb.Save
wb.Close
oXL.Quit
End Sub
Have to include a range in the source argument.

This post has been edited by June7: 16 August 2010 - 07:09 PM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1