VB Script Task - Running

Question

VB Script Task - Running

ringovski

Hall of Fame

Points: 3521
More actions
April 16, 2018 at 7:38 pm

#344382

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

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

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

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 1

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: 3521 More actions · Answer 2

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: 3521 More actions · Answer 3

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: 18355 More actions · Answer 4

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: 90860 More actions · Answer 5

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: 3521 More actions · Answer 6

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