Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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