Combining two VB Scripts for manipulating Excel into one

[montanan]

I use the following script frequently, for manipulating Excel data.

Code: Select all

SRT>UseExcel
//Put this VBSTART..VBEND block at top of script to declare the functions once
VBSTART
Dim xlApp
Dim xlBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)
end sub

'Closes the Excel file later
Sub CloseExcel
   xlApp.quit
   Set xlApp = Nothing
End Sub

'Retrieves a cell value from the specified worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row,Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function

VBEND
End>UseExcel

I just found a new script for sorting columnar data.

Code: Select all

VBSTART
Sub SortExcelFile(file,sort_column)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Workbooks.Open(file)
  xlApp.ActiveWorkbook.ActiveSheet.Range("A1").Sort xlApp.ActiveWorkbook.ActiveSheet.Range(sort_column),,,,,,,0
  xlApp.ActiveWorkBook.Save
  xlApp.ActiveWorkBook.Close false
  xlApp.quit
End Sub
VBEND

I'm trying to combine the latter one into the former one, so that I can call the functions as needed in subsequent projects. Others can probably use this as well.

I don't know VB Script very well, but I tried to edit the subroutine & include it in the single script at the top as follows ...

Sub SortExcelFile(filename,sort_column)
xlBook.ActiveSheet.Range("A1").Sort
xlBook.Range(sort_column),,,,,,,0
xlBook.Save
End Sub

... and then call the routines with ...

VBRun>OpenExcelFile,c:\filer\tosort.xls
VBRun>SortExcelFile,c:\filer\tosort.xls,A1


Nothing happens, so I guess my syntax needs some work! If one of you who knows VB Script can help fix this, I suspect others can also use this in their programs too.

[adroege]

Maybe an explanation of how VBScript and Macro Scheduler interact will help here.

Basically VBScript is a completely separate programming language from the macro script language. Anything VBScript needs to be entirely within the VBStart and VBEnd statements. Macro script commands CANNOT be inside the VBStart/VBEnd.

If all the VBScript code is contained within subroutines and functions then macro script commands execute first regardless of where the VBStart/VBEnd block is encountered in your code. The subroutines and functions contained within VBStart/VBEnd is only executed when a macro script statement of VBEval or VBRun is encountered.

Another potential problem is that you probably need to Dim xlBook globally (i.e. outside of all subroutines and functions) so that a single instance of this object can be seen by all subroutines and functions.

Also be sure you only create the object just once: i.e
Set xlApp = CreateObject("Excel.Application")

Hopefully this gets you back on track. If not, please post your script in it's entirety so that it's easier for forum members to spot the problems.