Solved

Importing an Excel Spreadsheet into Access when I do not know the ending range

Posted on 2013-01-26
6
317 Views
Last Modified: 2013-01-28
Hello,

I am having a hard time with something that I know should not be this hard.  I have an excel spreadsheet that I need to import into an Access table each month.  I know the starting range value (a5) but the file can change size each month so I do not know the ending range value.

In the past the file had the word total at the end so I just did a loop with DoCmd.TransferSpreadsheet acImport and kept checking (using a DLookup) for the word total to appear.  That is how I knew to stop the loop.

Since this file has nothing at the end but empty space I tried looking for null or blank but it will not work.  It runs and says that it is trying to import cells out of the range (error 3673).

Any ideas?
0
Comment
Question by:alevin16
6 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 38823310
Hello,

Is it possible for you to provide the code you're using? I would like to see how you're doing this so I can better understand what is going on.

thanks!

ab
0
 
LVL 33

Expert Comment

by:Norie
ID: 38823313
How exactly are you importing the worksheet?

Are you importing line by line?

Usually you don't need to specify the range, especially if you are using TransferSpreadsheet.

What happens if you don't specify a range?

Does the spreadsheet import but have some unwanted rows?
0
 
LVL 10

Assisted Solution

by:Anthony Berenguel
Anthony Berenguel earned 50 total points
ID: 38823317
"Usually you don't need to specify the range, especially if you are using TransferSpreadsheet."

I had the same thought, imnorie.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 150 total points
ID: 38823319
if you know the number of columns, say A to M, you can do this

for a2003
docmd.transferspreadsheet acimport,8, "tableName", "c:\folderName\myExcel.xls", true, "NameOfSheet!A5:M500"

for a2007


docmd.transferspreadsheet acimport,10, "tableName", "c:\folderName\myExcel.xlsx", true, "NameOfSheet!A5:M500"
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 300 total points
ID: 38823332
If the number of rows is completely unknowable, you may want to open the file in Excel first to determine the last row.



Dim xlApp As Object, xlWb As Object, xlWs As Object
Dim LastR As Long

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\folder\subfolder\book.xlsx")
Set xlWs = xlWb.Worksheets("Worksheet Name")

With xlWs
    LastR = .Cells(.Rows.Count, "a").End(-4162).Row '-4162 = xlUp
End With

xlWb.Close False
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

Open in new window


Now, when you do your TransferSpreadsheet, use something like this for the range argument:

"'Worksheet Name'!A5:Z" & LastR

Adjust the columns as needed, of course.
0
 

Author Closing Comment

by:alevin16
ID: 38829434
Thanks to everyone.  I was able to get thru it
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

749 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