Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
VBSCRIPT Read from Excelfile but now without Excel installation on a server

Answered VBSCRIPT Read from Excelfile but now without Excel installation on a server

  • Tuesday, May 01, 2012 12:56 PM
     
      Has Code
    Sign In to Vote
    0

    Hi to all,

    We have a vbscript in use to add objects to an LDAP database from Excel. The Excelfile contains the Hostname the MAC address and the Target-OU of the object to add.

    The vbscripts reads the values from the Excel and creates the objects. What we need for every row is the variable "word"  who contains the three parameters from Excel and delimited with ";".

    word=MSRL-108840;002655AD563A;MSRL

    Until now this script used to run on client machines with Excel installed. This is the code snippet we used.

    Set excelPath = WScript.Arguments
WScript.Echo "Reading Data from " & excelPath(0)
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = 0
objExcel.Workbooks.open excelPath(0), false, true
workSheetCount = objExcel.Worksheets.Count
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)
usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
top = currentWorksheet.UsedRange.Row
Set Cells = currentWorksheet.Cells
For row = 1 to (usedRowsCount-1)
column1=1
column2=2
column3=3
curRow = row+top
word = Cells(curRow,column1).Value & ";" & Cells(curRow,column2).Value & ";" & Cells(curRow,column3).Value

    Now we like to run this script on a server (Windows Server 2008 R2 64bit) where is no Excel available. I think this should be possible with the "Provider=Microsoft.ACE.OLEDB.12.0" Provider but I'm unable to create the code with this method to get the same result.

    Can you guys help me out of this mess? I can't get it done with the available examples.

    Any Help Would Be Greatly Appreciated

    Thanks and regards

    mat

     

All Replies

  • Tuesday, May 01, 2012 12:24 PM
     
      Has Code
    Sign In to Vote
    0

    We have a vbscript in use to add objects to an LDAP database from Excel. The Excelfile contains the Hostname the MAC address and the Target-OU of the object to add.

    The vbscripts reads the values from the Excel and creates the objects. What we need for every row is the variable "word"  who contains the three parameters from Excel and delimited with ";".

    word=MSRL-108840;002655AD563A;MSRL

    Until now this script used to run on client machines with Excel installed. This is the code snippet we used.

    Set excelPath = WScript.Arguments
WScript.Echo "Reading Data from " & excelPath(0)
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = 0
objExcel.Workbooks.open excelPath(0), false, true
workSheetCount = objExcel.Worksheets.Count
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)
usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
top = currentWorksheet.UsedRange.Row
Set Cells = currentWorksheet.Cells
For row = 1 to (usedRowsCount-1)
column1=1
column2=2
column3=3
curRow = row+top

    word = Cells(curRow,column1).Value & ";" & Cells(curRow,column2).Value & ";" & Cells(curRow,column3).Value

    Now we like to run this script on a server (Windows Server 2008 R2 64bit) where is no Excel available. I think this should be possible with the "Provider=Microsoft.ACE.OLEDB.12.0" Provider but I'm unable to create the code with this method to get the same result.

    Can you guys help me out of this mess? I can't get it done with the available examples.

    Any Help Would Be Greatly Appreciated

    Thanks and regards

    mat

    • Merged by Mark Liu-lxf Wednesday, May 02, 2012 3:51 AM duplicate thread
    •  
     
  • Tuesday, May 01, 2012 12:51 PM
     
     
    Sign In to Vote
    1

    I would recommend posting VBScript questions to The Official Scripting Guys Forum!.

    BTW, if you use Excel automation (as in your example) then Excel would need to be installed. You can update an Excel Workbook, without the Excel Application, if you use ADO and the ACE OLEDB Provider.

    Paul ~~~~ Microsoft MVP (Visual Basic)

     
  • Tuesday, May 01, 2012 12:59 PM
     
     
    Sign In to Vote
    0
    That's an easy question.  Your script will only work on a machine with Excel installed.  There is no workaround.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

     
  • Tuesday, May 01, 2012 1:29 PM
     
      Has Code
    Sign In to Vote
    0

    Hi to all,

    We have a vbscript in use to add objects to an LDAP database from Excel. The Excelfile contains the Hostname the MAC address and the Target-OU of the object to add.

    The vbscripts reads the values from the Excel and creates the objects. What we need for every row is the variable "word"  who contains the three parameters from Excel and delimited with ";".

    word=MSRL-108840;002655AD563A;MSRL

    Until now this script used to run on client machines with Excel installed. This is the code snippet we used.

    Set excelPath = WScript.Arguments
WScript.Echo "Reading Data from " & excelPath(0)
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = 0
objExcel.Workbooks.open excelPath(0), false, true
workSheetCount = objExcel.Worksheets.Count
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)
usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
top = currentWorksheet.UsedRange.Row
Set Cells = currentWorksheet.Cells
For row = 1 to (usedRowsCount-1)
column1=1
column2=2
column3=3
curRow = row+top
word = Cells(curRow,column1).Value & ";" & Cells(curRow,column2).Value & ";" & Cells(curRow,column3).Value

    Now we like to run this script on a server (Windows Server 2008 R2 64bit) where is no Excel available. I think this should be possible with the "Provider=Microsoft.ACE.OLEDB.12.0" Provider but I'm unable to create the code with this method to get the same result.

    Can you guys help me out of this mess? I can't get it done with the available examples.

    Any Help Would Be Greatly Appreciated

    Thanks and regards

    mat

    Yes - that is correct.  The ADO component can open Excel and read it but not like a GUI.  It reads it like a table.  Each table is a sheet or a named range.

    Use the ADO connection object to access the file.

    Look in the repository for scripts that show hopw this is accomplished.

    Grant - you are correct that the posted script will not work without Excel.  The ADO methoid works even without Excel.

    ¯\_(ツ)_/¯

     
  • Tuesday, May 01, 2012 1:32 PM
     
     
    Sign In to Vote
    0

    Here is a starter

    http://gallery.technet.microsoft.com/scriptcenter/ec7dedc6-8215-4cc0-9305-a1450bf7e23b

    ¯\_(ツ)_/¯

     
  • Tuesday, May 01, 2012 1:35 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Here is a little more extensiv eof an example.

    ReadExcel "d:\testit.xls"
Function ReadExcel(strFileName)
    
    Set objExcelConn = CreateObject("ADODB.Connection")
    objExcelConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & ";Extended Properties=Excel 5.0;Persist Security Info=False"
    Set objExcelRS = objExcelConn.Execute("SELECT * FROM [Sheet1$]")
    Do Until objExcelRS.EOF
	UpdateADUser objExcelRS.Fields(0), objExcelRS.Fields(1)
        objExcelRS.MoveNext
    Loop
End Function
Function UpdateADUser( sUserID, sEmployeeID )
        WScript.Echo sUserID, sEmployeeID 
End Function

    ¯\_(ツ)_/¯

    • Marked As Answer by maettu99 Wednesday, May 02, 2012 11:59 AM
    •  
     
  • Tuesday, May 01, 2012 1:39 PM
     
     
    Sign In to Vote
    0

    OT:  I won a copy of Sapien PrimalForms from the Scripting Games.  Should I be excited about it (not downloaded yet)?

    Does it enable scripting in various languages?

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

     
  • Tuesday, May 01, 2012 2:07 PM
     
     
    Sign In to Vote
    0

    OT:  I won a copy of Sapien PrimalForms from the Scripting Games.  Should I be excited about it (not downloaded yet)?

    Does it enable scripting in various languages?

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    OT: No.

    I this very good for building PowerShell WinForms.  I use it frequently.

    ¯\_(ツ)_/¯