CODEWITHSUNDEEP
excelvbscripthp-uft
John Mburu
John Mburu

Reputation: 11

Vbscript Class with functions to extract Excel data

i have a VB Script Class that has functions that extract specific excel data. the problem is, each function has to open excel and thus choke my machine when the number of excel files opened become many. is there a way to open excel only once, and then have my functions extract the data?

Here is my code.

Class TestData
    'User Name
    Function usrNm(filepath)    
        set ObjExcel = CreateObject("Excel.application")
        ObjExcel.workbooks.open ""&filepath& "\TestData\PosTestData.xls"    
            U_NAME = ObjExcel.sheets(1).cells(2,1)
            usrNm = U_NAME  
    End Function

    'User Password
    Function usrPwd(filepath)
        set ObjExcel = CreateObject("Excel.application")
        ObjExcel.workbooks.open ""&filepath& "\TestData\PosTestData.xls"        
            U_PASS = ObjExcel.sheets(1).cells(2,2)
            usrPwd = U_PASS
    End Function
End Class

Upvotes: 0

Views: 1273

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

A class with just a function and no member data makes no sense. If you .Close the Workbook and .Quit Excel in usrPwd(), the function 'works' stand-alone/without a class.

If you insist on OOP, create/open the Excel.Application in Class_Initialize and .Quit it in Class_Terminate; .Open and .Close the Workbook in the method usrPwd().

Class TestData
    Private ObjExcel

    Sub Class_Initialize
        Set ObjExcel = CreateObject("Excel.application")
    End Sub

    Sub Class_Terminate
        objExcel.Quit
    End Sub

    Function UserName(filepath)    
        ObjExcel.workbooks.open filepath & "\TestData\PosTestData.xls"    
        UserName = ObjExcel.sheets(1).cells(2,1)
    End Function

    Function UserPassword(filepath)
        ObjExcel.workbooks.open filepath & "\TestData\PosTestData.xls"        
        UserPassword = ObjExcel.sheets(1).cells(2,2)
    End Function
End Class

Upvotes: 1

Related Questions