Solved

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

Posted on 2013-01-26
6
307 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

896 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

13 Experts available now in Live!

Get 1:1 Help Now