• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

How to select a worksheet in an Excel workbook for import to Access 2000

I presently have a macro using TransferSpreadsheet.
It imports the excel worksheet ok but only the first worksheet in the workbook.

If I use the import wizard under Get External Data I can select individual worksheets.

I have 14 worksheets in the workbook and I would like to be able to import each to a seperate table by either using code or a macro attached to a command button on a form.

Thanks,
Ivan
0
icarey
Asked:
icarey
1 Solution
 
Jonathan KellyCommented:
you can use the Range property of the TransferSpreadsheet method if u know the sheet names.

eg.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "TestTable", "C:\Test.XLS", False, "YourSheetName"



0
 
icareyAuthor Commented:
Datrias,
This is the method I tried after reading the help on the docmd and in using a macro.

I tried again with your help but I still get an error message saying that the database engine could not find the object with the sheet name.

Thanks,
Ivan
0
 
Jonathan KellyCommented:
icarey
i get the same error when using a macro but if
you insert ! after the Range it works !!!

eg.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "TestTable", "C:\Test.XLS", False, "YourSheetName!"



0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
KMANCommented:
Not sure about the ! but my experience says to add a $.

Sheetname = Customers
XLS file = data.xls

Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "TestTable", "C:\data.xls", False, "Customers$"

Perhaps both are OK.

HTH, K

0
 
jack49aCommented:
I use VBA.  It is a bit complicated since you have to do a number of things including finding the number of worksheets and then looking at each one.  For my purposes I find it best to import each sheet in an import table, move data to a second table and update there and then move to my final table.  I do this so if there is an error in the import or the import is what is not expected then the user can back out without messing to the final table in the database.

The code below should explain what I do.  Be glad to answer questions but I won't be around from Sat-Weds next week so try to get back to me today.

Jack

Private Sub GetWorkbook()
Dim objWorkbook As Excel.Workbook
Dim objSheet As Worksheet
Dim strWBname As String
Dim strWSname As String
Dim i As Integer
Dim intCount As Integer


On Error GoTo GetWorkbook_Err
SendKeys "n"           ' to answer Macro question

Set objWorkbook = GetObject("" & strFilename & "")
                'global variable with path and filename

objWorkbook.Application.Cursor = xlWait
  'since Excel has control need to set Excel cursor

  strWBname = objWorkbook.Name
 intCount = objWorkbook.Sheets.Count

For i = 1 To (intCount)
   'Debug.Print i; intCount
 Set objSheet = objWorkbook.Sheets(i)
   strWSname = objSheet.Name
   If (strWSname = "Component" Or strWSname = "inj_std") Then GoTo skip
      'Debug.Print strWSname
       
 
    Call ImpExcel(strWSname)  'call sub to import Excel Data
                           
   
 End If
 
 
skip:
Next i

objWorkbook.Application.Quit    ' When you finish, use the Quit method to close
Set objWorkbook = Nothing    ' the application, then release the reference.
Set objSheet = Nothing

Call ResCleanup         'when done call sub to clean up


GetWorkbook_Exit:
    Exit Sub
GetWorkbook_Err:
       MsgBox Err.Number & Err.Description
    Resume GetWorkbook_Exit


End Sub






Private Sub ImpExcelRes(strAnal As String)

   '*************   repeats for each strWSname section begin
   
    DoCmd.TransferSpreadsheet acImport, 5, "tImpResTSQnew", strFilename, True, "" &

strWSname & "!A5:AP250"
    Call ImpResTSQTable1
       'sub moves data to tImpResTSQ2 and empties tImpResTSQnew
         'at this point column analyte is null
    DoCmd.RunSQL "UPDATE tImpResTSQ2 set analyte = '" & strWSname & "' where analyte is

null;"
    '****************   section end
   
End Sub
0
 
icareyAuthor Commented:
Datrias,
The import now works.

Thankyou for your answer.

Ivan
0
 
icareyAuthor Commented:
Thanks to all

The use of $ at the end of the range name also works

Ivan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now