473,320 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Import Multiple Sheets From Excel Into Access

Hi,

I am very new to VBA and am trying to set up an import from excel into access. Both are the 2010 version. I pulled the code I am using from a previous thread posted by a user trying to do what I want to do. The problem I am having is that every time I try to run the code I get a message saying "Compile Error: Invalid Outside Procedure" and the file path is highlighted. Any advice on what I am doing wrong would be greatly appreciated. Below is the code I am using. Thanks.

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Option Compare Database
  4.  
  5.  
  6.  
  7. Dim excelapp As New Excel.Application
  8. Dim excelbook As New Excel.Workbook
  9. Dim excelsheet As New Excel.Worksheet
  10. Dim intNoOfSheets As Integer, intCounter As Integer
  11. Dim strFilePath As String, strLastDataColumn As String
  12. Dim strLastDataRow As String, strLastDataCell As String
  13.  
  14. strFilePath = "S:\share\Innoware Quality System\Data\Ridigity\2011 Rigidity\2011 Performa Rigidity.xlsx"
  15.  
  16. Set excelbook = excelapp.Workbooks.Open(strFilePath)
  17.  
  18. intNoOfSheets = excelbook.Worksheets.Count
  19.  
  20. For intCounter = 1 To intNoOfSheets
  21.   excelbook.Worksheets(intCounter).Activate
  22.     'The next 3 lines will obtain the last data cell reference for each Worksheet
  23.     strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
  24.     strLastDataRow = Selection.SpecialCells(xlLastCell).Row
  25.     strLastDataCell = strLastDataColumn & strLastDataRow    'e.g. J123
  26.       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel14, "RigidityResultsExcelImport", strFilePath, False, _
  27.                  excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
  28. Next
  29.  
  30. excelbook.Close
  31. excelapp.Quit
  32. Set excelapp = Nothing
  33.  
  34.  
May 12 '11 #1
6 3495
You need to put the code into a subroutine or function. Add Public Sub ImportData before the first Dim statement, and End Sub to the end of the code block.
Jun 22 '11 #2
Well since I first posted this I have spent a lot of time working on it. It still will not function properly though. When I try to step into it, it stops at the highlighted line of code and give me this error: Run time error 1004. Excel cannot access '2011 Rigidity' The document may be read only or encrypted. But the document is not read only nor encrypted. This is what I have so far:


Expand|Select|Wrap|Line Numbers
  1. Public Sub importing()
  2. Dim excelapp As New Excel.Application
  3. Dim excelbook As New Excel.Workbook
  4. Dim excelsheet As New Excel.Worksheet
  5. Dim intNoOfSheets As Integer, intCounter As Integer
  6. Dim strFilePath As String, strLastDataColumn As String
  7. Dim strLastDataRow As String, strLastDataCell As String
  8.  
  9.   'The following is the path to the Excel file to open and import from
  10. strFilePath = "S:\share\Innoware Quality System\Data\Ridigity\2011 Rigidity"
  11.  
  12.   'The following opens the excel workbook & counts the tabs
  13. This is where the Debug option takes me to>>Set excelbook = excelapp.Workbooks.Open(strFilePath)  
  14. intNoOfSheets = excelbook.Worksheets.Count
  15.  
  16. For intCounter = 1 To intNoOfSheets
  17.   excelbook.Worksheets(intCounter).Activate
  18.  
  19.     'The next 3 lines will obtain the last data cell reference for each Worksheet
  20.     strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
  21.     strLastDataRow = Selection.SpecialCells(xlLastCell).Row
  22.     strLastDataCell = strLastDataColumn & strLastDataRow    'e.g. J123
  23.  
  24.       'The next line will import the data from excel into Access, into the table "ImportedRigidityResults"
  25.       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportedRigidityResults", strFilePath, False, _
  26.                  excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
  27. Next
  28.  
  29. excelbook.Close
  30. excelapp.Quit
  31. Set excelapp = Nothing
  32.  
  33. End Sub
Jun 22 '11 #3
I just used this code to Import a mutli page spreadsheet of my own. The two things I had to do to get it to run were: Added a Reference to Microsoft Excel 12.0 Object Library and then I also added the extension to the File name in the variable strFilePath (.xls)
Jun 22 '11 #4
Ok, I added the .xls, now the code runs through until the point where it is supposed to find the last used cell in each worksheet. The error code I get is : "Run-time '91': Object variable or with block variable not set. Then it highlights the noted line of code and dies.


Expand|Select|Wrap|Line Numbers
  1. Public Sub importing()
  2. Dim excelapp As New Excel.Application
  3. Dim excelbook As New Excel.Workbook
  4. Dim excelsheet As New Excel.Worksheet
  5. Dim intNoOfSheets As Integer, intCounter As Integer
  6. Dim strFilePath As String, strLastDataColumn As String
  7. Dim strLastDataRow As String, strLastDataCell As String
  8.  
  9.   'The following is the path to the Excel file to open and import from
  10. strFilePath = "S:\share\Innoware Quality System\Data\Ridigity\2011 Rigidity\2011 Performa Rigidity.xls"
  11.  
  12.   'The following opens the excel workbook & counts the tabs
  13. Set excelbook = excelapp.Workbooks.Open(strFilePath)
  14.  
  15. intNoOfSheets = excelbook.Worksheets.Count
  16.  
  17. For intCounter = 1 To intNoOfSheets
  18.   excelbook.Worksheets(intCounter).Activate
  19.  
  20.     'The next 3 lines will obtain the last data cell reference for each Worksheet
  21.     The code dies here >>strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)    strLastDataRow = Selection.SpecialCells(xlLastCell).Row
  22.     strLastDataCell = strLastDataColumn & strLastDataRow    'e.g. J123
  23.  
  24.       'The next line will import the data from excel into Access, into the table "ImportedRigidityResults"
  25.       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportedRigidityResults", strFilePath, False, _
  26.                  excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
  27. Next
  28.  
  29. excelbook.Close
  30. excelapp.Quit
  31. Set excelapp = Nothing
  32.  
  33. End Sub
Jun 22 '11 #5
That's unusual, I'm not recieving any errors at that point. If all of the data on your worksheets is the same size you could just remove that code and set the size of the range in the DoCmd line.
Jun 22 '11 #6
Unfortunately, the size of the data on each worksheet varies and so to eliminate blank results from being imported, I need the code to find the last used cell and import everything up to that point.
Jun 22 '11 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: PrinStation | last post by:
I am looking to import data from Excel to Access. The data is set up in one Excel file, but on 12 (monthly) sheets. After importing the first month/sheet (which I did successfully), I was looking...
4
by: N/A | last post by:
Hi, Is it possible to import data from Excel for doing numerical analysis in Python? If so how? Thank u!
3
by: lblanton | last post by:
I am trying to import an excel file with multiple worksheets into a single access table. The worksheets are formatted the same and with the same column headings but have varying rows. How do I import...
1
by: tezza98 | last post by:
I need some help. Im using a dtsrun command to import 9 tables into an Access database, most of the tables have about 1000 rows, but one has 20000+ rows and grows everyday. Im Using Access 2003...
1
by: Haidee | last post by:
Hi I'm pretty new to MS Access so please be patient.... I have a user that is trying to import an external excel spreadsheet into MS access 2003 by using the File - Get external data - Import. ...
1
by: DThreadgill | last post by:
I have a spreadsheet with about 35 tabs that I need to import into one table in Access. I found this link http://www.thescripts.com/forum/thread650662-import+multiple+tabs.html and changed some...
1
by: CF FAN | last post by:
Can Report Builder Export Excel Files with multiple Sheets How can I create a report that it can export in excel by multiple
7
by: ivancycheng | last post by:
I have around 400 excel files with same format (multiple sheets) and wants to import few sheets from excel to MS Access (XP version) for further processing. any one can advise me how to do it? (I'm...
2
by: BlackEyedPea | last post by:
Hi I have no coding experience but am using access 2003 on XP in the hope that I can find some code that will.... Search a folder in my network & import any excel spreadsheets it finds within...
0
by: puneetmittal33 | last post by:
Hi, I am facing the following issues and hoping that someone out there would have a resolution to the issue: 1) I am currently working on a web application which handles the payroll data for...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.