VB script do not execute compelte VBA Macro? No saved file output.

[BusanW]

Hi,

I have been struggeling now with an issue that I cannot understand.
I have a VBA macro that I need to run from a VB Script. The Macro reades from a file, copies into a spreadsheet, copies to another workbook and then saves it to a csv file.
If I only run the VBA macro within excel, all works fine. But if I call the VBScript, it seems to perform only parts of the script,not writing .

I post my code here , grateful for any feedback on this issue.
Best, Mia

VBScript:
---------

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open("C:\testData.xlsm")

objExcel.DisplayAlerts = False
objExcel.Application.Visible = True
objExcel.sheets("sheet1").range("A1").activate

objExcel.Application.Run "Module4.OpenUpdateCopy()"
For Each objWrkBook in objExcel.Workbooks
objWrkBook.Saved = True
objWrkBook.Close
Next

objExcel.Application.Quit
objExcel.Application.DisplayAlerts = True
objExcel.Quit
Set objExcel = Nothing

------

VBA macro called from the script:
------

Sub OpenUpdateCopy()

Dim FilePath As String
FilePath = "C:\Input_mini.csv"

Open FilePath For Input As #1

row_number = 0
Worksheets("Sheet1").Columns(1).ClearContents
Worksheets("Sheet1").Columns(2).ClearContents
Worksheets("Sheet1").Activate
Range("A1").Activate
Range("A1").Selecte

Do Until EOF(1)

Line Input #1, LineFromFile
Items = Split(LineFromFile, ";")

ActiveCell.Offset(row_number, 0).Value = Items(0)
ActiveCell.Offset(row_number, 1).Value = Items(1)

row_number = row_number + 1

Loop
Close #1

' part two
Set read_range = Sheets("Sheet1").Range("B1")
Set Write_range = Sheets("Sheet1").Range("C1")

Dim Name As String

i = 0
While Not read_range.Offset(i, 0).Value = ""

Name = CStr(read_range.Offset(i, 0).Value)

Write_range.Offset(i, 0).Value = """" & Name & """"
i = i + 1
Wend

Dim Pathname As String
Pathname = "C:\List.csv"

Set NewBook = Workbooks.Add
Workbooks("testData.xlsm").Worksheets("Sheet1").Range("A:C").Copy
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)

'Application.DisplayAlerts = False

'With ActiveWorkbook
NewBook.SaveAs Filename:=Pathname, FileFormat:=xlCSV
NewBook.Close SaveChanges:=True
'End With

'Workbooks("testData.xlsm").Close SaveChanges:=False
'Application.DisplayAlerts = True

End Sub

 

[SkipVought]

Hi,

I don't quite understand. Are you saying that this line does not execute?

objExcel.Application.Run "Module4.OpenUpdateCopy()"

This line I also do not understand

For Each objWrkBook in objExcel.Workbooks

What this says is that all the workbooks that are open in the instance of Excel that you have Set, you will loop through each workbook. Where are all these other workbooks?

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BusanW]

Hi, I will try to anwer below:

I don't quite understand. Are you saying that this line does not execute?
CODE
objExcel.Application.Run "Module4.OpenUpdateCopy()"

- No, not really, what I am trying to say is that it executes in the way that I can se that it opens excel, copies values, but then it does not open correctly a new workbook and save the output. So it either stops halfway or something is happening in the background but no csv output is produced.

This line I also do not understand
CODE
For Each objWrkBook in objExcel.Workbooks
What this says is that all the workbooks that are open in the instance of Excel that you have Set, you will loop through each workbook. Where are all these other workbooks?

- I open one workbook, then I copy the contents of column A:C and past into a new workbook (in the VBA code:
Set NewBook = Workbooks.Add
Workbooks("testData.xlsm").Worksheets("Sheet1").Range("A:C").Copy
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
)

I think there must be some issue for me that I have commands in the VBA macro, that somehow does not compile from the VB Script?

Thank you for your questions at least.
/Mia


???

 

[combo]

First, in the VBS comment [tt]objExcel.DisplayAlerts = False[/tt], at least during debugging. It executes default option and this may be not what you intend.

[tt]objExcel[/tt] is excel application object, so at the end of VBS code it's enough to:
[tt]objExcel.DisplayAlerts = True
objExcel.Quit
Set objExcel = Nothing[/tt]

Concerning number of workbooks, you close new workbook in VBA code ([tt]NewBook.Close SaveChanges:=True[/tt]), so in VBS you continue with one, at least with code you provided. That's what Skip points.

I'm not sure if VBS waits until VBA macro termination, if not, it may be a problem.

Why you split your code between VBS an VBA? You could execute all, at least all your example, directly in VBS, differences:
- access to text file: FileSystemObject,
- calling excel features (a general rule for calling procs in VBS): without named arguments (required order as in function description), without named excel constants (values instead).

combo