Importing Excel Worksheets

[greebo]

I have created a function for checking the Tab names in Excel workbooks before importing them (despite instructions, users will change the tab names before sending their monthly returns!)

*****************************************
Function fncXLTabCheck(strPath As String, strImpFile As String)

Dim appExcel As Object
Dim Wks As Worksheet

Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open strPath & strImpFile

For Each Wks In appExcel.Worksheets
If Trim(UCase(Wks.Name)) = "MONTH" Then
Wks.Name = "xxMonth-" & Format(Now(), "yyyymmddhhnn")
End If
Next

For Each Wks In appExcel.Worksheets
If Trim(UCase(Wks.CodeName)) = "MONTH" Then
Wks.Name = "Month"
End If
Next

ActiveWorkbook.Close SaveChanges:=True
Set appExcel = Nothing

End Function

*****************************************
For loop 1 finds any Tab named "Month" and changes it
For loop 2 finds the sheet with VBCodeName "Month" and changes the Tab Name to "Month"

This is part of an automatic application which is triggered by Scheduler and runs without user intervention, importing numerous excel files.

The first import works but the second stops at :
ActiveWorkbook.Close SaveChanges:=True
giving the error message "Object Variable or With block variable not set"

I could understand if the first import failed, but not the second

Any suggestions gratefully received

 

[aziz rasul]

Change

ActiveWorkbook.Close SaveChanges:=True

to

appExcel.ActiveWorkbook.Close SaveChanges:=True

and see if that works.

 

[greebo]

Working perfectly, Thanks for your help.

I generally get there eventually with Access VB but when it gets mixed with Excel, I am so far outside my comfort zone!

Cheers,

greebo