By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,908 Members | 1,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,908 IT Pros & Developers. It's quick & easy.

Get Last Column & Row of Data in Excel Worksheet

P: 57
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)

Expand|Select|Wrap|Line Numbers
  1. Dim excelapp As Excel.Application
  2. Dim excelbook As Excel.Workbook
  3. Dim excelsheet As Excel.Worksheet
  4. Dim intNoOfSheets As Integer, intCounter As Integer
  5. Dim strFilePath As String, strLastDataColumn As String
  6. Dim strLastDataRow As String, strLastDataCell As String
  7. Set cdl = New CommonDlg
  8. cdl.InitDir = CurrentProject.Path
  9. cdl.FileName = "*"
  10. cdl.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
  11. cdl.CancelError = True
  12. cdl.OpenFlags = cdlOFNAllowMultiselect Or cdlOFNNoChangeDir
  13. cdl.ShowOpen
  14. strfilename = cdl.FileName
  15.  
  16. Set excelbook = Excel.Application.Workbooks.Open(strfilename)
  17.  
  18. intNoOfSheets = excelbook.Worksheets.Count
  19.  
  20. For intCounter = 1 To intNoOfSheets
  21.    excelbook.Worksheets(intCounter).Activate
  22.  
  23.  strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
  24.     strLastDataRow = Selection.SpecialCells(xlLastCell).Row
  25.         If strLastDataRow = 1 Then
  26.         GoTo Line1
  27.         End If
  28.     strLastDataCell = strLastDataColumn & strLastDataRow
  29.  
  30.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel11, "Cart_Data", strfilename, True, _
  31.                  excelbook.Worksheets(intCounter).Name & "!A1:" & strLastDataCell
  32. Line1:
  33. Next
  34.  
  35. excelbook.Close
  36. Set excelbook = Nothing
  37. Excel.Application.Quit
  38. Set excelapp = Nothing
  39.  
  40. End Function
Not sure where to go from here and hoping that the experts can help me out.

Also, I understand what the
Expand|Select|Wrap|Line Numbers
  1. Selection.SpecialCells(xlLastCell).Column 
does, but what does the + 64 do?
Nov 27 '07 #1
Share this Question
Share on Google+
22 Replies


Denburt
Expert 100+
P: 1,356
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 TransferSpreadsheet 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.SpecialCells(xlLastCell).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
Nov 28 '07 #2

NeoPa
Expert Mod 15k+
P: 31,769
Indeed the code is trying to produce a character (Chr(...)) as Denburt surmises.
Selection.SpecialCells(xlLastCell) 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.
Nov 28 '07 #3

Denburt
Expert 100+
P: 1,356
Indeed the code is trying to produce a character (Chr(...)) as Denburt surmises.
Selection.SpecialCells(xlLastCell) 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. :)
Nov 28 '07 #4

NeoPa
Expert Mod 15k+
P: 31,769
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.
Nov 28 '07 #5

JustJim
Expert 100+
P: 407
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
Nov 28 '07 #6

ADezii
Expert 5K+
P: 8,705
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 TransferSpreadsheet 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.SpecialCells(xlLastCell).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).
Expand|Select|Wrap|Line Numbers
  1. If Selection.SpecialCells(xlLastCell).Column <= 26 Then
  2.   strLastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
  3. Else
  4.   strLastDataColumn = "A" & Chr((Selection.SpecialCells(xlLastCell).Column Mod 26) + 64)
  5. End If
  6.  
Nov 29 '07 #7

NeoPa
Expert Mod 15k+
P: 31,769
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.
Expand|Select|Wrap|Line Numbers
  1. 'ToA1 converts numeric co-ordinates to A1 notation.
  2. Private Function ToA1(Optional lngRow1 As Long = 0, _
  3.                       Optional intCol1 As Integer = 0, _
  4.                       Optional lngRow2 As Long = 0, _
  5.                       Optional intCol2 As Integer = 0, _
  6.                       Optional strFixed As String = "$") As String
  7.     Dim strCol As String
  8.  
  9.     If lngRow2 = 0 And intCol2 > 0 Then lngRow2 = lngRow1
  10.     If intCol2 = 0 And lngRow2 > 0 Then intCol2 = intCol1
  11.     strCol = IIf(intCol1 > 26, Chr(Asc("A") + (intCol1 \ 26) - 1), "")
  12.     strCol = strCol & Chr(Asc("A") + (intCol1 - 1) Mod 26)
  13.     ToA1 = IIf(intCol1 > 0, strFixed & strCol, "")
  14.     ToA1 = ToA1 & IIf(lngRow1 > 0, strFixed & lngRow1, "")
  15.     If lngRow2 > 0 Or intCol2 > 0 Then _
  16.         ToA1 = ToA1 & ":" & ToA1(lngRow2, intCol2, , , strFixed)
  17. End Function
Nov 29 '07 #8

NeoPa
Expert Mod 15k+
P: 31,769
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.
Nov 29 '07 #9

JustJim
Expert 100+
P: 407
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
Nov 29 '07 #10

ADezii
Expert 5K+
P: 8,705
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.
Expand|Select|Wrap|Line Numbers
  1. 'ToA1 converts numeric co-ordinates to A1 notation.
  2. Private Function ToA1(Optional lngRow1 As Long = 0, _
  3.                       Optional intCol1 As Integer = 0, _
  4.                       Optional lngRow2 As Long = 0, _
  5.                       Optional intCol2 As Integer = 0, _
  6.                       Optional strFixed As String = "$") As String
  7.     Dim strCol As String
  8.  
  9.     If lngRow2 = 0 And intCol2 > 0 Then lngRow2 = lngRow1
  10.     If intCol2 = 0 And lngRow2 > 0 Then intCol2 = intCol1
  11.     strCol = IIf(intCol1 > 26, Chr(Asc("A") + (intCol1 \ 26) - 1), "")
  12.     strCol = strCol & Chr(Asc("A") + (intCol1 - 1) Mod 26)
  13.     ToA1 = IIf(intCol1 > 0, strFixed & strCol, "")
  14.     ToA1 = ToA1 & IIf(lngRow1 > 0, strFixed & lngRow1, "")
  15.     If lngRow2 > 0 Or intCol2 > 0 Then _
  16.         ToA1 = ToA1 & ":" & ToA1(lngRow2, intCol2, , , strFixed)
  17. End Function
Hello NeoPa, you realllllllllllly like those IIfs, don't you? If it wasn't for you, this Function would have been obsolete years ago! (LOL).
Nov 29 '07 #11

Denburt
Expert 100+
P: 1,356
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
Expand|Select|Wrap|Line Numbers
  1. i = Selection.SpecialCells(xlLastCell).Column
  2. If i <= 26 Then
  3.     LastColumn = Chr(i + 64)
  4. Else
  5.     x = (i Mod 26)
  6.     i = (i \ 26)
  7. LastColumn = Chr(64 + i) & Chr(64 + x)
  8. end if
  9.  
That should return the last column utilizing the approach in the OP.
Questions/comments/suggestions are always welcome.
Nov 29 '07 #12

Denburt
Expert 100+
P: 1,356
BTW that is a nifty routine Neo and thanks for throwing yourself to us dogs. lol Just wait.
Nov 29 '07 #13

Denburt
Expert 100+
P: 1,356
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.SpecialCells(xlLastCell).Column)

Neo's function posted earlier:
Expand|Select|Wrap|Line Numbers
  1. Private Function ToA1(Optional lngRow1 As Long = 0, _
  2.                       Optional intCol1 As Integer = 0, _
  3.                       Optional lngRow2 As Long = 0, _
  4.                       Optional intCol2 As Integer = 0, _
  5.                       Optional strFixed As String = "$") As String
  6.     Dim strCol As String
  7.     If lngRow2 = 0 And intCol2 > 0 Then lngRow2 = lngRow1
  8.     If intCol2 = 0 And lngRow2 > 0 Then intCol2 = intCol1
  9.     strCol = IIf(intCol1 > 26, Chr(Asc("A") + (intCol1 \ 26) - 1), "")
  10.     strCol = strCol & Chr(Asc("A") + (intCol1 - 1) Mod 26)
  11.     ToA1 = IIf(intCol1 > 0, strFixed & strCol, "")
  12.     ToA1 = ToA1 & IIf(lngRow1 > 0, strFixed & lngRow1, "")
  13.     If lngRow2 > 0 Or intCol2 > 0 Then _
  14.         ToA1 = ToA1 & ":" & ToA1(lngRow2, intCol2, , , strFixed)
  15. End Function
  16.  
Nov 29 '07 #14

ADezii
Expert 5K+
P: 8,705
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
Expand|Select|Wrap|Line Numbers
  1. i = Selection.SpecialCells(xlLastCell).Column
  2. If i <= 26 Then
  3.     LastColumn = Chr(1 + 64)
  4. Else
  5.     x = (i Mod 26)
  6.     i = (i \ 26)
  7. LastColumn = Chr(64 + i) & Chr(64 + x)
  8. end if
  9.  
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).
Nov 30 '07 #15

NeoPa
Expert Mod 15k+
P: 31,769
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
I was spelling it out because other people may read the thread too Jim. I have no worries on that score where you're concerned :)
Nov 30 '07 #16

NeoPa
Expert Mod 15k+
P: 31,769
Hello NeoPa, you realllllllllllly like those IIfs, don't you? If it wasn't for you, this Function would have been obsolete years ago! (LOL).
Quite the contrary in my view ADezii.
There are some issues with the IIf() function, but they rarely cause problems if you know what they are and avoid them (as I do). Otherwise it can be a very succinct and clear way to assign values and various other small tasks within the code.
As the matter has been raised, I'll just clarify what the issues are (for anyone else reading the thread) :
The VBA version of IIf() (doesn't apply to IIf() called from SQL) has a drawback in that all parameters are resolved within the processing regardless of whether the first parameter resolves to TRUE or FALSE.
Hence, a call of the form :
Expand|Select|Wrap|Line Numbers
  1. IIf(TestVal = "A", Long_Running_Function_A, Long_Running_Function_B)
Would end up running both of the (long running) functions. This is inefficient coding (certainly not to be advised).
However, I can see no reason to advise against the following usage :
Expand|Select|Wrap|Line Numbers
  1. Result = IIf(TestVal < "N", Chr(&H41), Chr(&H5A))
Nov 30 '07 #17

NeoPa
Expert Mod 15k+
P: 31,769
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
Expand|Select|Wrap|Line Numbers
  1. i = Selection.SpecialCells(xlLastCell).Column
  2. If i <= 26 Then
  3.     LastColumn = Chr(1 + 64)
  4. Else
  5.     x = (i Mod 26)
  6.     i = (i \ 26)
  7. LastColumn = Chr(64 + i) & Chr(64 + x)
  8. end if
That should return the last column utilizing the approach in the OP.
Questions/comments/suggestions are always welcome.
Sorry to come in late with so many posts, but you guys were busy here while I was away all yesterday so I'm trying to catch up ;)
Den, I would change line three to reference i instead of the 1.
FYI if anyones interested here is a snipet of Neo's working example to retrieve the last column in an A1 style.
Expand|Select|Wrap|Line Numbers
  1. Debug.Print ToA1(, Selection.SpecialCells(xlLastCell).Column)
It's true you can use the commas to separate out the parameters, but I always recommend using the parameter names in cases where there are optional parameters. As in :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print ToA1(intCol1:=Selection.SpecialCells(xlLastCell).Column)
BTW that is a nifty routine Neo and thanks for throwing yourself to us dogs. lol Just wait.
Thank you, and no problems. You (scurvy) dogs do at least ask questions that make sense and are properly expressed, so that's actually easier than dealing with other questions anyway ;D
Nov 30 '07 #18

NeoPa
Expert Mod 15k+
P: 31,769
...
I just remembered, I must be more careful since you do outrank me! (LOL).
Don't let technical rank (Oh where is that Life of Brian sketch when you need it) worry you ADezii. We certainly don't! You're still seen as one of the most valuable members of the team (EVEN when you disagree with me tsk tsk :D).
Nov 30 '07 #19

P: 57
Wow - I appreciate the input on this and will review and stay away from ranges where all possible as suggested. Ya'll have all been a great help - thank you!
Nov 30 '07 #20

Denburt
Expert 100+
P: 1,356
O.K. thanks for catching that Neo Line 3 has been changed.

ADezii, your fired! Wait don't leave yet I need some more ideas, inspiration you know, and when you get done giving me inspiration you can go back and write some more tips for us. j/k Your input is always highly regarded.

It's true you can use the commas to separate out the parameters, but I always recommend using the parameter names in cases where there are optional parameters.
Your absolutely right on that point.

There are some issues with the IIf() function, but they rarely cause problems if you know what they are and avoid them (as I do). Otherwise it can be a very succinct and clear way to assign values and various other small tasks within the code.
As the matter has been raised, I'll just clarify what the issues are (for anyone else reading the thread) :
The VBA version of IIf() (doesn't apply to IIf() called from SQL) has a drawback in that all parameters are resolved within the processing regardless of whether the first parameter resolves to TRUE or FALSE.
Another example would be the procedure I wrote earlier, if I were to write is as an iif statement it would produce an error when you use any column over 191 because the character set only has 255 characters and your already adding 64.
Nov 30 '07 #21

Denburt
Expert 100+
P: 1,356
Wow - I appreciate the input on this and will review and stay away from ranges where all possible as suggested. Ya'll have all been a great help - thank you!
Glad we could help and since you have the two methods now, you should be able to implement either of them into your procedure if it is needed. Let us know if there is anything else we can help with or if you have any issues implementing the procedures.
Nov 30 '07 #22

NeoPa
Expert Mod 15k+
P: 31,769
...Another example would be the procedure I wrote earlier, if I were to write is as an iif statement it would produce an error when you use any column over 191 because the character set only has 255 characters and your already adding 64.
Absolutely right there Denburt. Any usage that relied on only one side executing (depending on the first parameter check) would suffer from this problem and one or other of the other parameters could fail (crash) upon execution. Definitely worth bearing in mind.
Nov 30 '07 #23

Post your reply

Sign in to post your reply or Sign up for a free account.