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
Solved

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

Posted on 2001-06-08
7
494 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
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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