Solved

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

Posted on 2001-06-08
7
497 Views
Last Modified: 2012-08-14
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
Comment
Question by:icarey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 6166899
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
 
LVL 3

Author Comment

by:icarey
ID: 6167450
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
 
LVL 7

Accepted Solution

by:
Jonathan Kelly earned 100 total points
ID: 6167483
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Expert Comment

by:KMAN
ID: 6167601
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
 
LVL 2

Expert Comment

by:jack49a
ID: 6167620
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
 
LVL 3

Author Comment

by:icarey
ID: 6167625
Datrias,
The import now works.

Thankyou for your answer.

Ivan
0
 
LVL 3

Author Comment

by:icarey
ID: 6167648
Thanks to all

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

Ivan
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question