Solved

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

Posted on 2001-06-08
7
489 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now