.End(xlUp).Row not working in Access

[Vagus14]

I can't get the code in red below to work. How would I go about coding this section? Is it possible to code this part? "
"lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row 'Error 1004"

Errors recieved:
"1004" Application-defined or object-defined error

The objective is so it will look in column "F" for total count of data. Next line it adds + 1 and that's the cell it will insert the picture.

Thanks for your help.

Public Function AddITARPic()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
 
With xlApp
Dim wb As Object
Dim ws As Object
Dim lastRow As Long
Set wb = .Workbooks.Open("D:\Sample.xlsm")
Set ws = wb.Sheets(1)
 
[COLOR=seagreen]'Code below counts the cells with data and selects the last empty cell[/COLOR]
[COLOR=black]lastRow = ws.Cells(ws.Rows.Count, "F").[/COLOR][COLOR=red]End(xlUp).Row  'Error 1004[/COLOR]
[COLOR=black]Blankcell = lastRow + 1[/COLOR]
ws.Cells(Blankcell, "A").Select
ws.pictures.insert ("D:\sample.png")
 
xlApp.Visible = True
LinkToFile = False
SaveWithDocument = True
wb.Close
Set xlApp = Nothing
End With
End Function

 

[pbaldy]

Constants like xlUp typically don't work when using late binding. You can find the numerical equivalent, or there's another method to get the lady row that escapes me right now. I have it on another computer that I'll be on in a little bit if you need it.

 

[Vagus14]

Hey Paul,

Thanks for the help. How would I incorporate the numerical equivalent? I farily new to the coding process.

Lady row? Yeah any solution you can provide I'm all ears. Thanks!

 

[namliam]

Go into excel immediate window and type: ?xlUp

?xlup
-4162

Or google microsoft.com

 

[Brianwarnock]

Why are you finding the lastrow in Col F and then using this for column A ?

If all columns have the same number of data rows does
lastrow = ws.UsedRange.Rows.Count

work for Access code

Brian

 

[pbaldy]

The other method I mentioned:

lastRow = ws.cells.SpecialCells(11).row

 

[pbaldy]

Lady row? !

Ha! iPad autocorrect in action combined with failure to review before submitting. :banghead:

 

[Vagus14]

Thank you for everyone's help! I am new to VBA so you were very helpful. Vote up for everyone here and hopefully this thread will help others!

Public Function LoopAddPic()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
Dim wb As Object
Dim ws As Object
Dim lastRow As Long
Set wb = .Workbooks.Open("D:\Sample.xlsm")
Set ws = wb.Sheets(1)
'Code below counts the cells with data and selects the last empty cell
lastRow = ws.UsedRange.Rows.Count
'Cells(ws.Rows.Count, "F") '.End(xlUp).Row
Blankcell = lastRow + 1
ws.Cells(Blankcell, "F").Select
ws.pictures.insert ("D:\sample.png")
xlApp.Visible = True
LinkToFile = False
SaveWithDocument = True
wb.Close
Set xlApp = Nothing
End With
End Function

 

[Vagus14]

One more question, sorry. How would u get this to auto save and close with the same name? I did a google search but haven't found anything concrete.

 

[pbaldy]

Try

xlApp.ActiveWorkbook.Save
xlApp.Quit

 

[Vagus14]

For some reason the code opens the workbook in read only format. So when I do what you suggusted it prompts for a new file name. Is there a way to prevent this?

Here's the code I am using.

Public Function AddITARPic()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
Dim wb As Object
Dim ws As Object
Dim lastRow As Long
 
Set wb = .Workbooks.Open("D:\Sample.xlsm")
Set ws = wb.Sheets(1)
 
'Code below counts the cells with data and selects the last empty cell
lastRow = ws.UsedRange.Rows.Count
Blankcell = lastRow + 1
ws.Cells(Blankcell, "F").Select
ws.pictures.insert ("D:\sample.png")
 
xlApp.Visible = True
LinkToFile = False
SaveWithDocument = True
'ws.SaveAs FileName:="D:\" & BookName
wb.Close , [COLOR=red]SaveChanges:=True (The excel file is read only)[/COLOR]
Set xlApp = Nothing
End With
End Function

 

[pbaldy]

The spreadsheet isn't open already is it? I've never used that extension, so I don't know if that's the problem.

 

[Vagus14]

Paul,

Everything is working correctly. Thanks again for your help!

James

 

[pbaldy]

Happy to help James!