type mismatch error referencing Excel workbook 1

[KellyK]

I am trying to copy an Excel worksheet from one workbook into another through Access VBA. I have the file name set in a variable called txtReportFile.

I am getting a "type mismatch error" in the following line:

Sheets("Key-Factors").Copy Before:=Workbooks(txtReportFile).Sheets(1)

It works fine when I type the name of the file in quotes instead of using the variable, but I will not know the name of the file until the program is run, hence the variable. I believe it has something to do with the fact that txtReportFile contains a path AND a file (i.e. "C:\data\file.xls") instead of just "file.xls" but I don't know how to get just the file name. Any ideas how I can get this to work?

Kelly

 

[CautionMP]

KellyK,

Sheets("Key-Factors").Copy Before:=Workbooks([b]Mid(txtReportFile,InstrRev(txtReportFile,"\")+1))[/b]).Sheets(1)

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

How is the spreadsheet open ?
If like this:
Set myWB = myXL.Workbooks.Open(txtReportFile)

Then:
myXL.Sheets("Key-Factors").Copy Before:=myWB.Sheets(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KellyK]

CautionMP, worked like a charm! Thanks so much. PHV, I appreciate your response as well.

Kelly