0

I have a vbs script inside an automation process.
This script runs an Excel Macro splitting an Excel into multiple files based on row number. What happens is: the script creates some splitted files and then throws an error:

MacroSubSplit.vbs(20, 1) (null): L'oggetto invocato si è disconnesso dai client corrispondenti. that is "object invoked has disconnected from its clients"

The macro comes from this answer: How to split spreadsheet into multiple spreadsheets with set number of rows?

I have modified it to open a file f.

Public Sub Split_Auto(f As String)
  Dim wb As Workbook
  Dim ThisSheet As Worksheet

  Dim NumOfColumns As Integer
  Dim RangeToCopy As Range
  Dim WorkbookCounter As Integer
  Dim wbDst As Workbook
  Dim RowsInFile
  Dim Prefix As String

  Application.ScreenUpdating = False
'Seleziona file

 Dim fDialog As Office.FileDialog
 Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

 Set wbDst = Workbooks.Open(f)

  'Initialize data
  Set ThisSheet = wbDst.Worksheets("TotalData")
  NumOfColumns = ThisSheet.UsedRange.Columns.Count
  WorkbookCounter = 1
  RowsInFile = 40000                   'how many rows (incl. header) in new files?
  Prefix = "Split"                    'prefix of the file name

  For p = 1 To ThisSheet.UsedRange.Rows.Count Step RowsInFile
    Set wb = Workbooks.Add

'Paste the chunk of rows for this file
Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 1, NumOfColumns))
RangeToCopy.Copy wb.Sheets(1).Range("A1")

'Save the new workbook, and close it
strDate = Format(Now, "dd/MMM/yyyy")
wb.SaveAs ThisWorkbook.Path & "\" & Prefix & Format(Now, "yyyy-MM-dd") & "_SplitNum_" & WorkbookCounter
wb.Close

'Increment file counter
WorkbookCounter = WorkbookCounter + 1


 Next p

  Application.ScreenUpdating = True
  Set wb = Nothing

End Sub

The macro is called from a vbs file 

Option Explicit

if WScript.Arguments.Count = 0 then
    WScript.Echo "Missing parameters"
    WScript.Quit    
end if
Dim xlApp, xlBook, File, objShell, input_path

File = WScript.Arguments.Item(0)

Set xlApp = CreateObject("Excel.Application")
set objShell = WScript.CreateObject ("WScript.Shell")
'WScript.Echo(objShell.CurrentDirectory)
input_path = objShell.CurrentDirectory & "\Input\" & File
'WScript.Echo("Input" & input_path)
'close without prompting
xlApp.DisplayAlerts = False
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open(objShell.CurrentDirectory&"\"&"Macro.xlsm", 0, True)
xlApp.Run "Foglio1.Split_Auto", CStr(input_path)
xlBook.Close False 
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

'WScript.Echo "Finished."
WScript.Quit

Can you help me to find the solution to this problem?

Franktrt
  • 373
  • 1
  • 8
  • 18
  • Is any of the macro being called do you know? – Nathan_Sav Mar 08 '19 at 10:41
  • No. This is the only script calling the Excel macro @Nathan_Sav – Franktrt Mar 08 '19 at 11:10
  • Yes, but it calls a loop, does any of the loop get called. Have you put any checks in, like a macro called "Module1.Test", simply outputting a text file or saving itself as a file? – Nathan_Sav Mar 08 '19 at 11:42
  • What's with all the Excel Macro questions today is it annual Excel Macro run from VBScript day or something? – user692942 Mar 08 '19 at 12:20
  • @Nathan_Sav I made a message box output in the loop. It is called once and showd the counter. At the third iteration, it gave the error – Franktrt Mar 08 '19 at 12:55
  • Sorry @Lankymart, if I understand, this scrips should run once a day – Franktrt Mar 08 '19 at 12:57
  • Maybe asking the obvious, but this is working as a macro in excel, called just from calling `Foglio1.Split_Auto` – Nathan_Sav Mar 08 '19 at 13:52
  • @Nathan_Sav It is working after OS restart. What could be the reason? – Franktrt Mar 08 '19 at 15:43
  • Not sure, might be an idea if it happens again, to check task manager to see how many instances of Excel you have running. It's happened to me, where without thinking, coding with Excel being not visible has left a lot open. Did you develop it showing the Excel object it creates? – Nathan_Sav Mar 08 '19 at 15:54
  • @Nathan_Sav, I first killed all active Excel instances. It seems something randomic – Franktrt Mar 08 '19 at 16:10
  • How many workbooks will this be opening? May be try defining `p` as with a step of 40k each time, could be worth while defining this as a long. – Nathan_Sav Mar 08 '19 at 16:18
  • Maybe some `doevents` as well in the macro, when closing etc. – Nathan_Sav Mar 08 '19 at 16:36

0 Answers0