VBscript to query excel spreadsheet

[snooc1]

I'm very new to VBScripting and am hoping that someone can be of some help, and have a feeling that it's rather simple...

I have an inventory spreadsheet of PC's serial numbers (column K) and asset tags (column J). This is located on a network share (\\server\path\inventory.xls). I need a script that I can run using the serial number as a variable to return the asset tag.
For example, the command "script.vbs 'column k'" would return the value of the matching "column j".

Can anyone help me???

 

[SkipVought]

hi,

1 your Excel sheet needs Headings in ROW 1, starting in column A.

2. assuming that the headings for the data of interest are serial number[/b[ & asset tag

3. your sheet name is Sheet1

Function GetAssetTag(sn As String)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
     
    sPath = "D:\My Documents\_Databases\_Excel"   'your path
    sDB = "ResourceData"                          'your excel file
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT [asset tag] "
    sSQL = sSQL & "FROM [Sheet1$] "
    sSQL = sSQL & "WHERE [serial number]='" & sn & "' "
        
   On Error Resume Next

    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

        .MoveFirst
 
        If Err.Number = 0 Then
            GetAssetTag = rst(0)
        Else
            GetAssetTag = ""
        End If
        
        .Close
    End With
    
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[snooc1]

Wow, Skip, thanks for the fast reply!!

I've named this script "getasset.vbs", and am running it from a command prompt like this: getasset.vbs D3500D1

"D3500D1 is the serial number I'm attempting to find the asset tag for. However, I get the following error:

Line: 1
Char: 25
Error: Expected ')'
Code: 800A03EE
Source: Microsoft VBScript compilation error

Here is the modified script. I've only edited the column headers, filename, path, and the extension from .xls to .xlsx. Any ideas?

Function GetAssetTag(sn As String)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
     
    sPath = "C:\Users\E048321\Desktop"	'your path
    sDB = "MWR-ITInventoryTemplate desktops"	'your excel file
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xlsx;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT [TWC Asset #] "
    sSQL = sSQL & "FROM [Sheet1$] "
    sSQL = sSQL & "WHERE [Service Tag/Serial Num]='" & sn & "' "
        
   On Error Resume Next

    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

        .MoveFirst
 
        If Err.Number = 0 Then
            GetAssetTag = rst(0)
        Else
            GetAssetTag = ""
        End If
        
        .Close
    End With
    
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

 

[SkipVought]

What does Line: 1 Char: 25 refer to? Line 1 in the FUNCTION or line 1 in your calling procedure?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[snooc1]

Line 1 in the FUNCTION.

 

[SkipVought]

Strange, there IS a ) at character 25 on that line???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[snooc1]

That's why I'm confused. It seems to hang up at "As String" in that line. In fact, when I remove all of the "As ..." portions, it completes, but outputs nothing...

 

[jges]

In VBScript you cannot declare variables as types (string, integer, double, etc). Everything is treated as a variant with type conversions largely taken care of behind the scenes. There are pros and cons to this approach, but one consequence is an error will be thrown if you try to use "As String", "As Integer", "As Double", etc etc.

If you want to use command line arguments when you call the function, that will need to be taken care of by your calling procedure. It would look something like this:

retvar = GetAssetTag(WScript.Arguments(0))

Function GetAssetTag(sn)
.
.
.

Of course you will want to add some validation to the command line input before using it and you may want to allow for multiple command line input.

 

[snooc1]

Guys, again, I appreciate the help.
Ok, so I modified it to look like this:

retvar = GetAssetTag(WScript.Arguments(0))

Function GetAssetTag(sn)
    Dim sConn, sSQL
    Dim rst, cnn
    Dim sPath, sDB
     
    sPath = "C:\Users\E048321\Desktop"   'your path
    sDB = "MWR-ITInventoryTemplate desktops"                          'your excel file
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xlsx;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT [TWC Asset #] "
    sSQL = sSQL & "FROM [Sheet1$] "
    sSQL = sSQL & "WHERE [Service Tag/Serial Num]='" & sn & "' "
        
   On Error Resume Next

    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

        .MoveFirst
 
        If Err.Number = 0 Then
            GetAssetTag = rst(0)
        Else
            GetAssetTag = ""
        End If
        
        .Close
    End With
    
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Now I'm getting the error:
Line 11
Char 5
Class not defined: 'ADODB'

Any ideas???

 

[PHV]

Set cnn = CreateObject("ADODB.Connection")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[snooc1]

Thanks for the input. I'm now getting this error after changing both of those lines:
Line 20
Char 5
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.

Here is what I currently have:

retvar = GetAssetTag(WScript.Arguments(0))

Function GetAssetTag(sn)
    Dim sConn, sSQL
    Dim rst, cnn
    Dim sPath, sDB
     
    sPath = "C:\Users\E048321\Desktop"   'your path
    sDB = "MWR-ITInventoryTemplate desktops"                          'your excel file
    
    Set cnn = CreateObject("ADODB.Connection") 
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xlsx;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = CreateObject("ADODB.Recordset")
    
    sSQL = "SELECT [TWC Asset #] "
    sSQL = sSQL & "FROM [Sheet1$] "
    sSQL = sSQL & "WHERE [Service Tag/Serial Num]='" & sn & "' "
        
   On Error Resume Next

    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

        .MoveFirst
 
        If Err.Number = 0 Then
            GetAssetTag = rst(0)
        Else
            GetAssetTag = ""
        End If
        
        .Close
    End With
    
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

 

[SkipVought]

Check your ODBC drivers to find DSN=Excel Files or not, and change the connection string accordingly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

HAve a look here:

http://www.connectionstrings.com/excel-2007

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886