Formatting excel from MS Access

[E]

Is there a way to find a column letter in Excel in MS Access. I use this
function in Excel to come up with the column letter:

Function ColLetter(ColNumber As Integer) As String
ColLetter = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

If I try and add that to my Access module it bombs out on the "Cells" part.

Thanks

 

[David C. Holley]

Try posting an Excel group. Although you're automating Excel from within
Access, its still Excel that you're working with.

 

[Matt Williamson]

Is there a way to find a column letter in Excel in MS Access. I use this
function in Excel to come up with the column letter:

Function ColLetter(ColNumber As Integer) As String
ColLetter = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

If I try and add that to my Access module it bombs out on the "Cells"
part.

Thanks

You just need to reference the Excel object model or use Late binding with
CreateObject to instantiate excel. Once you do that you can reference excel
and Cells will be recognized. So it will look kinda like this. I'd use the
reference so you get intellisense but there are +/- for either method.

Air code (not tested)

dim objXL as Excel.Application
set objXL = new Excel.Application

Function ColLetter(ColNumber As Integer) As String
ColLetter = Left(objXL.Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

HTH

Matt

 

[E]

Matt,

Thanks, I will give this a try. It looks promising.

E

 

[E]

Matt,

Thanks again for your help. I had to change it a little but essentially
what you said worked. Here's the code in case someone sle has the same
question:

Function ColLetter(ColNumber As Integer) As String
Dim objXL As Excel.Application
Set objXL = New Excel.Application

With objXL
ColLetter = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End With

End Function