VB Script Task - Running

  • ringovski

    Hall of Fame

    Points: 3346

    Hi All,
    I am new to VB so not sure what the issue is. I am trying to open a .xls (mht) file and save it as .xlsx.

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Core
    Imports System.IO
    Imports System.Text
    Imports System.Windows.Forms
    Imports Microsoft.SqlServer.Dts.Runtime
       Public Sub Main()
            Dim xPath As String, xFile As String
            Dim oExcel As Microsoft.Office.Interop.Excel.Application = Nothing
            'Dim wb As Workbooks
            Dim wb As Microsoft.Office.Interop.Excel.Workbook = Nothing
            xPath = "C:\DSM Excel Files\Healthcare Logictics\Download\"
            xFile = xPath + "*.*.xls"
            wb = oExcel.Workbooks.Open(Filename:="C:\DSM Excel Files\Healthcare Logictics\Download\*.xls")
            wb.SaveAs(Filename:="C:\DSM Excel Files\Healthcare Logictics\*.xlsx")

            Dts.TaskResult = ScriptResults.Success
        End Sub

    The task just keeps running and doesn't stop, any suggestions?
    Thanks

  • Jonathan AC Roberts

    SSCoach

    Points: 16844

    It look like the script shouldn't run at all.

    you have
    you have Dim oExcel As New Microsoft.Office.Interop.Excel.Application = Nothing
    then later down you have:
    wb = oExcel.Workbooks.Open(Filename:="C:\DSM Excel Files\Healthcare Logictics\Download\*.xls")
    The oExcel object is set to nothing so you can't call any functions on it. You would have to create a New one.

    A spelling mistake in your folder name (Logictics)

    This line:  This line:  xFile = xPath + "*.*.xls" will set xFile to C:\DSM Excel Files\Healthcare Logictics\Download\*.*.xls (two asterisks) is that what you want?
    I don't think Excel allows you to specify wildcards on the filename of the open command. I think you would have to go through all the files in the folder one by one, the same with the Save command. 

    One of the easiest ways to write VBA code is to record a Macro of your actions then take that code out and amend it.

  • ringovski

    Hall of Fame

    Points: 3346

    Thanks for the reply.
    I have created a excel macro which works when I run it in excel. I am trying to run it via the script task. But It comes with error "object reference not set to an instance of object".

     Dim ObjExcel As Microsoft.Office.Interop.Excel.Application = Nothing
     ObjExcel.Application.Run("C:\xxxxx\yyyyyyy\Book1.xlsm")
    ObjExcel.Quit
     ObjExcel = Nothing

    Any suggestions?

  • ringovski

    Hall of Fame

    Points: 3346

    I have also tried creating a script.vbs with

    Dim objexcel As New Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
                wb = objexcel.Workbooks.Open(Filename:="C:\DSM Excel Files\Healthcare Logictics\Download\Z_ZVSDR274_PRINCMTD_00000.xls")
                wb.SaveAs("C:\DSM Excel Files\Healthcare Logictics\Z_ZVSDR274_PRINCMTD_00000.xlsx")           
                objexcel.Quit
                ObjExcel = Nothing

    Then a run.bat

    Cscript script.vbs "C:\DSM Excel Files\Healthcare Logictics\Book1.xlsm"

    running .bat manually doesn't work.bat

  • Jonathan AC Roberts

    SSCoach

    Points: 16844

    VB Script is different from normal VB, you can't declare a variable to be a type. e.g. you can't do "Dim i as integer" you just have to do: "Dim i"
    So in vbscript "Dim objexcel As New Microsoft.Office.Interop.Excel.Application" won't work, it would come up with a runtime error. You would have to write the code in vbScript something like this:
    Dim objexcel
    Set objexcel = CreateObject('Microsoft.Office.Interop.Excel.Application')
    Dim wb
    wb = objexcel.Workbooks.Open(Filename:="C:\DSM Excel Files\Healthcare Logictics\Download\Z_ZVSDR274_PRINCMTD_00000.xls")
    wb.SaveAs("C:\DSM Excel Files\Healthcare Logictics\Z_ZVSDR274_PRINCMTD_00000.xlsx")
    objexcel.Quit
    ObjExcel = Nothing

  • Sue_H

    SSC Guru

    Points: 90169

    ringovski - Monday, April 16, 2018 7:38 PM

    Hi All,
    I am new to VB so not sure what the issue is. I am trying to open a .xls (mht) file and save it as .xlsx.

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Core
    Imports System.IO
    Imports System.Text
    Imports System.Windows.Forms
    Imports Microsoft.SqlServer.Dts.Runtime
       Public Sub Main()
            Dim xPath As String, xFile As String
            Dim oExcel As Microsoft.Office.Interop.Excel.Application = Nothing
            'Dim wb As Workbooks
            Dim wb As Microsoft.Office.Interop.Excel.Workbook = Nothing
            xPath = "C:\DSM Excel Files\Healthcare Logictics\Download\"
            xFile = xPath + "*.*.xls"
            wb = oExcel.Workbooks.Open(Filename:="C:\DSM Excel Files\Healthcare Logictics\Download\*.xls")
            wb.SaveAs(Filename:="C:\DSM Excel Files\Healthcare Logictics\*.xlsx")

            Dts.TaskResult = ScriptResults.Success
        End Sub

    The task just keeps running and doesn't stop, any suggestions?
    Thanks

    If you don't really need to open the file, you can rename it with FileSystemObject.MoveFile which doesn't seem as complicated -
    Dim objFso
    Set objFso= CreateObject("Scripting.FileSystemObject")
    objFso.MoveFile "D:\Files\YourFile.xls", "D:\Files\YourFile.xlsx"

    Sue

  • ringovski

    Hall of Fame

    Points: 3346

    Thanks for the replies.
    I got it working with  a excel macro. I recorded the macro in excel doing what I wanted this using a .BAT and .VBS script and works perfectly.


    .BAT
    cscript  "C:\aaaaaaa\yyyyyyyyyy\Macro\Script.vbs"
    exit

    .VBS
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\xxxxxxxxxx\yyyyyyyyyy\Macro\Book1.xlsm")
    objExcel.Application.Visible = False
    objExcel.Application.Run "Macro1"
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit
    WScript.Echo "Finished."
    WScript.Quit

    Cheers

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply