Get Last Column & Row of Data in Excel Worksheet

[DThreadgill]

Hi all - having a bit of problem with this code. It works in one of the databases that I have but not the one I'm currently working with...I've compared references and they're both the same:

When trying to get the last column of data from each worksheet in my Excel file, it's giving me the last column as "c" when it should be "AH" or "AI,' depending on which worksheet it's on. On one worksheet, it's giving me "]" and there is no "]" anywhere in the worksheet. The lastdatarow number is correct - just the column that isn't right.

Here's the code I'm using (which I got from here-many thanks to the contributor)

Dim excelapp As Excel.Application
Dim excelbook As Excel.Workbook
Dim excelsheet As Excel.Worksheet
Dim intNoOfSheets As Integer, intCounter As Integer
Dim strFilePath As String, strLastDataColumn As String
Dim strLastDataRow As String, strLastDataCell As String
Set cdl = New CommonDlg
cdl.InitDir = CurrentProject.Path
cdl.FileName = "*"
cdl.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
cdl.CancelError = True
cdl.OpenFlags = cdlOFNAllowMultiselect Or cdlOFNNoChangeDir
cdl.ShowOpen
strfilename = cdl.FileName
 
Set excelbook = Excel.Application.Workbooks.Open(strfilename)

intNoOfSheets = excelbook.Worksheets.Count
 
For intCounter = 1 To intNoOfSheets
   excelbook.Worksheets(intCounter).Activate
  
 [B]strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)[/B]
    strLastDataRow = Selection.SpecialCells(xlLastCell).Row
        If strLastDataRow = 1 Then
        GoTo Line1
        End If
    strLastDataCell = strLastDataColumn & strLastDataRow
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel11, "Cart_Data", strfilename, True, _
                 excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
Line1:
Next
 
excelbook.Close
Set excelbook = Nothing
Excel.Application.Quit
Set excelapp = Nothing

End Function

Not sure where to go from here and hoping that the experts can help me out.

Also, I understand what the

Selection.SpecialCells(xlLastCell).Column

does, but what does the + 64 do?

[Denburt]

My suggestion would be to remove the range since it it appears that you are trying to import all the data from the spreadsheet. If I am mistaken then let me know. I looked over the code you supplied and would like to try to answer your question regarding the +64. I would say that it looks like someone made an attempt at returning a letter from the alphabet (Character Set) using the Chr function to be used in the range for the TransferSpreads heet function. If the numbers of your columns to be imported exceeds 26 then you would have more issues with this code.
Another reason I suggest removing the range is due to the fact that Selection.Speci alCells(xlLastC ell).Column will not always return the last column. I have seen instances where it would be benificial fore something like this I myself try to stay away from using a range during import, due to the number of issues that pop up for something like this.
Hope this helps

[NeoPa]

Indeed the code is trying to produce a character (Chr(...)) as Denburt surmises.
Selection.Speci alCells(xlLastC ell) will give the last cell currently used in the worksheet (or, more precisely, the intersection of the last column used and the last row used).
This may not appear to be the one you want if the column it returns :

• Is hidden
• Has been deleted but the workbook has not been saved since
• The contents have been cleared but not deleted

Remember, this is not the last column with visible data in it. It is equivalent to returning the cell selected after you type Ctrl-End within Excel.

[Denburt]

Indeed the code is trying to produce a character (Chr(...)) as Denburt surmises.
Selection.Speci alCells(xlLastC ell) will give the last cell currently used in the worksheet (or, more precisely, the intersection of the last column used and the last row used).
This may not appear to be the one you want if the column it returns :

• Is hidden
• Has been deleted but the workbook has not been saved since
• The contents have been cleared but not deleted

Remember, this is not the last column with visible data in it. It is equivalent to returning the cell selected after you type Ctrl-End within Excel.

Thanks for the clarifications Neo sounds like I may have an Excel goto guy now. :)

[NeoPa]

Of course you can.
My Excel experience is stronger in some ways than my Access. Don't forget to draw my attention to the question though. I'm afraid I no longer get to check each one out as I used to.

[JustJim]

Of course you can.
My Excel experience is stronger in some ways than my Access. Don't forget to draw my attention to the question though. I'm afraid I no longer get to check each one out as I used to.

Hmm, thats noted!

Jim

[ADezii]

My suggestion would be to remove the range since it it appears that you are trying to import all the data from the spreadsheet. If I am mistaken then let me know. I looked over the code you supplied and would like to try to answer your question regarding the +64. I would say that it looks like someone made an attempt at returning a letter from the alphabet (Character Set) using the Chr function to be used in the range for the TransferSpreads heet function. If the numbers of your columns to be imported exceeds 26 then you would have more issues with this code.
Another reason I suggest removing the range is due to the fact that Selection.Speci alCells(xlLastC ell).Column will not always return the last column. I have seen instances where it would be benificial fore something like this I myself try to stay away from using a range during import, due to the number of issues that pop up for something like this.
Hope this helps

Hello Denburt, just a useless piece of information. It would be relatively easy to allow for a large number of Columns in the code, such as the snippet below which would allow up to 52 Columns ( A to AZ).
[CODE=vb]
If Selection.Speci alCells(xlLastC ell).Column <= 26 Then
strLastDataColu mn = Chr(Selection.S pecialCells(xlL astCell).Column + 64)
Else
strLastDataColu mn = "A" & Chr((Selection. SpecialCells(xl LastCell).Colum n Mod 26) + 64)
End If
[/CODE]

[NeoPa]

If you find it interesting, I have developed a function to convert from R1C1 (or similar) style references into A1 style. I include it in case it's useful either to use natively, or to use the concept simply to convert a column. It does handle columns which go beyond Z and it can convert columns on their own as well as a full cell reference, or even range references.

'ToA1 converts numeric co-ordinates to A1 notation.
Private Function ToA1(Optional lngRow1 As Long = 0, _
                      Optional intCol1 As Integer = 0, _
                      Optional lngRow2 As Long = 0, _
                      Optional intCol2 As Integer = 0, _
                      Optional strFixed As String = "$") As String
    Dim strCol As String

    If lngRow2 = 0 And intCol2 > 0 Then lngRow2 = lngRow1
    If intCol2 = 0 And lngRow2 > 0 Then intCol2 = intCol1
    strCol = IIf(intCol1 > 26, Chr(Asc("A") + (intCol1 \ 26) - 1), "")
    strCol = strCol & Chr(Asc("A") + (intCol1 - 1) Mod 26)
    ToA1 = IIf(intCol1 > 0, strFixed & strCol, "")
    ToA1 = ToA1 & IIf(lngRow1 > 0, strFixed & lngRow1, "")
    If lngRow2 > 0 Or intCol2 > 0 Then _
        ToA1 = ToA1 & ":" & ToA1(lngRow2, intCol2, , , strFixed)
End Function

[NeoPa]

Hmm, thats noted!

Jim

That's an invitation to all Experts and Moderators of this site, but you can consider yourself included Jim. What I'm talking about is the dispensation to PM me the notice of course. Anyone can post questions any time, but you guys can PM me notice of it's existence if you like and I'll give it my attention.
As per the site rules, I'm normally not very happy to receive PMs about technical matters, but in cases like these I'm happy to relax that for youse guys.
Obviously, the question should be posted as a thread just like any other.

[JustJim]

That's an invitation to all Experts and Moderators of this site, but you can consider yourself included Jim. What I'm talking about is the dispensation to PM me the notice of course. Anyone can post questions any time, but you guys can PM me notice of it's existence if you like and I'll give it my attention.
As per the site rules, I'm normally not very happy to receive PMs about technical matters, but in cases like these I'm happy to relax that for youse guys.
Obviously, the question should be posted as a thread just like any other.

No, no. I wouldn't dream of PM'ing you an actual question. I just meant that I would note that you have experience coding for Excel as well. I appreciate the privilege of being invited to draw your attention to forum questions in this area. I have in fact done this before as a courtesy when starting forum discussions of other people's (what used to be) Articles.

Jim

[ADezii]

If you find it interesting, I have developed a function to convert from R1C1 (or similar) style references into A1 style. I include it in case it's useful either to use natively, or to use the concept simply to convert a column. It does handle columns which go beyond Z and it can convert columns on their own as well as a full cell reference, or even range references.

'ToA1 converts numeric co-ordinates to A1 notation.
Private Function ToA1(Optional lngRow1 As Long = 0, _
                      Optional intCol1 As Integer = 0, _
                      Optional lngRow2 As Long = 0, _
                      Optional intCol2 As Integer = 0, _
                      Optional strFixed As String = "$") As String
    Dim strCol As String

    If lngRow2 = 0 And intCol2 > 0 Then lngRow2 = lngRow1
    If intCol2 = 0 And lngRow2 > 0 Then intCol2 = intCol1
    strCol = IIf(intCol1 > 26, Chr(Asc("A") + (intCol1 \ 26) - 1), "")
    strCol = strCol & Chr(Asc("A") + (intCol1 - 1) Mod 26)
    ToA1 = IIf(intCol1 > 0, strFixed & strCol, "")
    ToA1 = ToA1 & IIf(lngRow1 > 0, strFixed & lngRow1, "")
    If lngRow2 > 0 Or intCol2 > 0 Then _
        ToA1 = ToA1 & ":" & ToA1(lngRow2, intCol2, , , strFixed)
End Function

Hello NeoPa, you reallllllllllll y like those IIfs, don't you? If it wasn't for you, this Function would have been obsolete years ago! (LOL).

[Denburt]

Some really cool input on this thread anyone want more. O.K. I couldn't help myself I broke down and spent a couple of minutes this morning looking this over. Thanks to ADezii
[code=vb]
i = Selection.Speci alCells(xlLastC ell).Column
If i <= 26 Then
LastColumn = Chr(i + 64)
Else
x = (i Mod 26)
i = (i \ 26)
LastColumn = Chr(64 + i) & Chr(64 + x)
end if
[/code]

That should return the last column utilizing the approach in the OP.
Questions/comments/suggestions are always welcome.

[Denburt]

BTW that is a nifty routine Neo and thanks for throwing yourself to us dogs. lol Just wait.

[Denburt]

FYI if anyones interested here is a snipet of Neo's working example to retrieve the last column in an A1 style.

Debug.Print ToA1(, Selection.Speci alCells(xlLastC ell).Column)

Neo's function posted earlier:
[code=vb]
Private Function ToA1(Optional lngRow1 As Long = 0, _
Optional intCol1 As Integer = 0, _
Optional lngRow2 As Long = 0, _
Optional intCol2 As Integer = 0, _
Optional strFixed As String = "$") As String
Dim strCol As String
If lngRow2 = 0 And intCol2 > 0 Then lngRow2 = lngRow1
If intCol2 = 0 And lngRow2 > 0 Then intCol2 = intCol1
strCol = IIf(intCol1 > 26, Chr(Asc("A") + (intCol1 \ 26) - 1), "")
strCol = strCol & Chr(Asc("A") + (intCol1 - 1) Mod 26)
ToA1 = IIf(intCol1 > 0, strFixed & strCol, "")
ToA1 = ToA1 & IIf(lngRow1 > 0, strFixed & lngRow1, "")
If lngRow2 > 0 Or intCol2 > 0 Then _
ToA1 = ToA1 & ":" & ToA1(lngRow2, intCol2, , , strFixed)
End Function
[/code]

[ADezii]

Some really cool input on this thread anyone want more. O.K. I couldn't help myself I broke down and spent a couple of minutes this morning looking this over. Thanks to ADezii
[code=vb]
i = Selection.Speci alCells(xlLastC ell).Column
If i <= 26 Then
LastColumn = Chr(1 + 64)
Else
x = (i Mod 26)
i = (i \ 26)
LastColumn = Chr(64 + i) & Chr(64 + x)
end if
[/code]

That should return the last column utilizing the approach in the OP.
Questions/comments/suggestions are always welcome.

Okay Denburt, yours is prettier and more compact than mine, but don't forget where you got the inspiration. I just remembered, I must be more careful since you do outrank me! (LOL).