Solved

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

Posted on 2013-01-26
6
302 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

759 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

20 Experts available now in Live!

Get 1:1 Help Now