Get Excel's cell value (format string "A1") (1 Viewer)

Status
Not open for further replies.

ogh

New member
Local time
Today, 09:20
Joined
Apr 23, 2008
Messages
1
I don't know if ayone has made this before, but in any case this is a code I made to get values from Excel cells to Access.

Usage:
Code:
[varialvel] = CellXLS("A1")

Source Code:
Code:
'Code created by Thiago "Ogh" Westphal Rescaroli
'contact ogh_wr@hotmail.com

Public xl As New Excel.Application
Public xlw As Excel.Workbook

'Sub to open an Excel Spreadsheet
Public Sub OpenXLS(fName as String)
    set xlw = xl.Workbooks.Open(fName)
End Sub

Private Function CellXLS(str As String)
    Dim i As Integer
    Dim lin, col, c As String
    
    'Transfor the string to lowercase for posterior ASCII value use
    str = LCase(str)
    For i = 1 To Len(str)
        c = Mid(str, i, 1) 'Get the char from str
        If Asc(c) >= 97 And Asc(c) <= 122 Then 'The codes 97 to 122 are the ASCII values of 'a' to 'z'
            col = col & c
        ElseIf Asc(c) >= 30 And Asc(c) <= 39 Then 'The codes 32 to 39 are the ASCII values of '0' to '9'
            lin = lin & c
        Else
            Exit Function
        End If
    Next i
    
    CellXLS = xlw.Application.Cells(CInt(lin), C26(col)).Value
    
End Function

'Function to convert the alphanumeric Excel collums base (26) to decimal base
Private Function C26(ByVal AlphaNum As String) As Integer
    Dim n, i, val As Integer
    Dim c As String
    
    'Transfor the string to lowercase for posterior ASCII value use
    AlphaNum = LCase(AlphaNum)
    'Iniciate the variavel val to don't return a Null value
    val = 0
    For i = Len(AlphaNum) To 1 Step -1
        c = Mid(AlphaNum, i, 1) 'Get the char from str
        n = Asc(c) - 96
        val = val + (n * (26 ^ (Len(AlphaNum) - i)))
    Next i
    
    C26 = val
End Function

Usage:
Code:
[varialvel] = CellXLS("A1")

Use it for free, but please don't remove the credits...
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom