[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-01-26
6
Medium Priority
?
321 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 35

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 200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 600 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 93

Accepted Solution

by:
Patrick Matthews earned 1200 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

834 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