xlDown function in Access

[andrea_bertorelli]

Dear all,

I am new to VBA programming and I need some support (actually as much support as I can get...). I created a module in Access that opens a workbook, copies data from one tab to another and then saves and closes the workbook. To capture a defined set of data in the tab I am using the xlDown function; the issue is that I get the "variable not defined" error, and if I declare it I get again an error, this time the "Sub or Function not defined" kind. attached is the code:

------------------------------
Option Compare Database
Option Explicit


Sub OpenSpecific_xlFile()
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String


Set oXL = CreateObject("Excel.Application")


On Error GoTo ErrHandle
sFullPath = CurrentProject.Path & "\01.S-DIFOT_NewSolution.xls"


With oXL
.Visible = True
.Workbooks.Open (sFullPath)
.Range("A2:K65536").Select
.Selection.ClearContents
.Sheets("SIFOT").Select
.Range("K92:A" & Range("A92").End(xlDown).Row).Select
.Selection.Copy
.Sheets("SDIFOT_IMPORT").Select
.Range("A2").Select
.Selection.PasteSpecial
.Range("A2").Select
.Application.CutCopyMode = False
.ActiveWorkbook.Save
.ActiveWorkbook.Close
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

----------------------------------------------

Could you shed some light? Your help is much appreciated.

Thanks
Andrea

 

[Brianwarnock]

Are you sure that you have the Excel object Library selected in your references?

Brian

 

[andrea_bertorelli]

Brianwarnock, I think the references are not selected. I tried selecting in it by going into "Tools" and then "References" in the VBA editor but I am afraid I was not able to find the proper reference to Excel. Which one should I thick off?

thanks.

 

[chergh]

You need a "." in front of your second range statement.

.Range("K92:A" & .Range("A92").End(xlDown).Row).Select

 

[Brianwarnock]

As you scroll down the references you will come to one something like microsoft Excel 10.0 Object Library your number may be different but that's the one.

Brian

 

[andrea_bertorelli]

Thanks Brianwarnock, indeed that's waht was needed (chergh, thanks for the tip but didn't work without the reference selected).

Brianwarnock, can I take advantage of your knowledge for one more question? I need to import a tab into a access table. I want to cancel the existing table first in order not to have the additional info appended and then import; I am looking at "DoCmd TransferSpreadsheet acImport" but could you help set the proper parameters for "Book1.xls" and "Sheet1" (this is a tab in the workbook).

thanks again.

 

[chergh]

It does work as you've written it without the references as long as you put the dot before the range, and it is preferable for it not to need references if it is going to be used by multiple people.


To delete the existing table:

currentdb.tabledefs.delete "TableName"

Transfer spreadsheet:

docmd.transferspreadsheet acimport,,"TableName","c:\book1.xls",true, "Sheet1"

If you do not have field names at the top of your spreadsheet then change the 'true' above to 'false'

 

[andrea_bertorelli]

Thanks Chergh. I tried again taking away the references and with the dot as suggested but the error "variable not defined" comes up again. I wanted to keep it "referenceless" as well because of the same reason you mentioned: other users may need to access the application. So, help me out... what do yuo think I am doing wrong? Thanks for the tips on the import.

 

[Brianwarnock]

The delay in my response was because I have not done this before and didn't want to give air code, I'm glad I didn't as I found while trialing that you have to put ! after the sheet name.


docmd.transferspreadsheet acimport,,"TableName","c:\book1.xls",true, "Sheet1!"

Note also that if the spreadsheet and database are in the same folder you donot need the path infact you must just say "book1.xls", unless this only applies if the folder is My Documents, hmm I'll have to set up a test sometime.


Chergh
I'm surprised that you say you don't need references, this is the second thread recently where the poster was having problems till they were selected.

Brian

 

[chergh]

My bad actually. I pasted the code into an access db that already had excel stuff in it and at the top of my code was the line:

Const xldown = -4121

Using xldown without references will result in error unless you use a constant as I have.

 

[Brianwarnock]

Thanks for clearing that up, I was scratching out what bit of hair I've got left.

Brian

 

[chergh]

Should have known better tbh.

Andrea> You also want to make a couple more changes to your code.

After workbooks.open you really want select your sheet otherwise it will open on which ever sheet it was saved on, well you actually really want to fully reference all your worksheets and get rid of the select and selection bits. e.g.

.Range("A2:K65536").Select
.Selection.ClearContents

will do the same as:

.Range("A2:K65536").ClearContents

Also get rid of the following in your error handling:

oXL.Visible = False

and you probably want to replace it with something like:

If oXL.Visible = False then
   oXL.quit
end if

or you could just replace it with oXL.quit though that may be a bit abrupt.