Merge VBScript files into one without error

Hi Everyone,

I have 4 VBScript files that adds named ranges per excel file.

I would like to ask for assistance on how to combine these 4 VBScripts into one VBScript file without having an error if any of the 4 excel files does not exist.


Here are the VBScripts:

Script # 1:

Code:

Set objExcel = CreateObject("Excel.Application") 
'Opens the Excel File
Set objWorkbook = objExcel.Workbooks.Open ("C:\PDB_Taiwan\INVENTORY\INVENTORY_Source_Files\TWN_INV_DISPLAY_TYPES.xls") 
Set objWorksheet = objWorkbook.Worksheets(1)
'Add Range Name to Off Take
objWorksheet.Range("$A:$N").Name = "TWN_INV_DISPLAY_TYPES"
objExcel.Workbooks(1).Save 'Save the workbook, not excel file 
objExcel.Workbooks(1).Close 'Close the workbook then finally quit excel 
objExcel.Quit 
MsgBox "TWN_INV_DISPLAY_TYPES Named Range defined.", vbInformation, "Done"

Script # 2:

Code:

Set objExcel = CreateObject("Excel.Application") 
'Opens the Excel File
Set objWorkbook = objExcel.Workbooks.Open ("C:\PDB_Taiwan\INVENTORY\INVENTORY_Source_Files\TWN_INV_MAJOR_PROD.xls") 
Set objWorksheet = objWorkbook.Worksheets(1)
'Add Range Name to Off Take
objWorksheet.Range("$A:$C").Name = "TWN_INV_MAJOR_PROD"
objExcel.Workbooks(1).Save 'Save the workbook, not excel file 
objExcel.Workbooks(1).Close 'Close the workbook then finally quit excel 
objExcel.Quit 
MsgBox "TWN_INV_MAJOR_PROD Named Range defined.", vbInformation, "Done"

Script # 3:

Code:

Set objExcel = CreateObject("Excel.Application") 
'Opens the Excel File
Set objWorkbook = objExcel.Workbooks.Open ("C:\PDB_Taiwan\INVENTORY\INVENTORY_Source_Files\TWN_INV_VISIT_PERIOD.xls") 
Set objWorksheet = objWorkbook.Worksheets(1)
'Add Range Name to Off Take
objWorksheet.Range("$A:$F").Name = "TWN_INV_VISIT_PERIOD"
objExcel.Workbooks(1).Save 'Save the workbook, not excel file 
objExcel.Workbooks(1).Close 'Close the workbook then finally quit excel 
objExcel.Quit 
MsgBox "TWN_INV_VISIT_PERIOD Named Range defined.", vbInformation, "Done"

Script # 4:

Code:

Set objExcel = CreateObject("Excel.Application") 
'Opens the Excel File
Set objWorkbook = objExcel.Workbooks.Open ("C:\PDB_Taiwan\INVENTORY\INVENTORY_Source_Files\TWN_INV_CSTMR.xls") 
'WS
Set objWorksheet = objWorkbook.Worksheets("WS")
'Add Range Name to WS
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_WS"
'CM
Set objWorksheet = objWorkbook.Worksheets("CM")
'Add Range Name to CM
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_CM"
'MF
Set objWorksheet = objWorkbook.Worksheets("MF")
'Add Range Name to MF
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_MF"
'WC
Set objWorksheet = objWorkbook.Worksheets("WC")
'Add Range Name to WC
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_WC"
'AM
Set objWorksheet = objWorkbook.Worksheets("AM")
'Add Range Name to AM
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_AM"
'DM
Set objWorksheet = objWorkbook.Worksheets("DM")
'Add Range Name to DM
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_DM"
'XT
Set objWorksheet = objWorkbook.Worksheets("XT")
'Add Range Name to XT
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_XT"
'HL
Set objWorksheet = objWorkbook.Worksheets("HL")
'Add Range Name to HL
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_HL"
'YC
Set objWorksheet = objWorkbook.Worksheets("YC")
'Add Range Name to YC
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_YC"
'FL
Set objWorksheet = objWorkbook.Worksheets("FL")
'Add Range Name to FL
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_FL"
'TT
Set objWorksheet = objWorkbook.Worksheets("TT")
'Add Range Name to TT
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_TT"
'MC
Set objWorksheet = objWorkbook.Worksheets("MC")
'Add Range Name to MC
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_MC"
'WP
Set objWorksheet = objWorkbook.Worksheets("WP")
'Add Range Name to WP
objWorksheet.Range("$A:$H").Name = "TWN_INV_CSTMR_WP"
'Save the workbooks, not excel file 
objExcel.Workbooks(1).Save 
'Close the workbooks then finally quit excel 
objExcel.Workbooks(1).Close 
objExcel.Quit 
MsgBox "TWN_INV_CSTMR Named Ranges defined.", vbInformation, "Done"

Thank you,
r0mmel

 

I assume the FileExists Method would help.

Code:

Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists("C:\PDB_Taiwan\INVENTORY\INVENTORY_Source_Files\TWN_INV_DISPLAY_TYPES.xls") Then
  ' process the file here
End If

Btw: There is no need to quit the excel application and create it new for each file if you merge the scripts.

Regards
GermanOne

 

Thanks GermanOne! Yes, this is exactly what I am looking for. You saved the day once again.