• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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

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
alevin16
Asked:
alevin16
3 Solutions
 
Anthony BerenguelCommented:
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
 
NorieVBA ExpertCommented:
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
 
Anthony BerenguelCommented:
"Usually you don't need to specify the range, especially if you are using TransferSpreadsheet."

I had the same thought, imnorie.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Rey Obrero (Capricorn1)Commented:
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
 
Patrick MatthewsCommented:
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
 
alevin16Author Commented:
Thanks to everyone.  I was able to get thru it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now