Error when saving excel file

[math20]

Hi,

I am trying to save an excel file with access vb script code.
The code I am using works the first time around but when I run it again it gives me an error:
Run Time Error 91
Object variable or with block variable npt set.

Here is the code:
Set r1 = wbk.Sheets("CTAS Total (2)").Range("A1:S65")
r1.Copy
wbk.Sheets("CTAS Total (2)").Select
r1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

wbk.Sheets("CTAS Total (2)").Copy


This is where I get the error:

ActiveWorkbook.SaveAs FileName:= _
"J:\cag\Network Information INFO,CSC_IVR\FISCAL 2008\New IBTM Report\Branch Transit Report\Report\Transit " & Forms!CTAS_BRANCH_REPORT!Text0 & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


wbk.Close ("CTAS BRANCH REPORT")

appExcel.Quit

I would really appreciate if any one can help me with this.

P.S. I had posted this request before but I can not find the thread. I apologize for posting it again.

Thank you for all your help.

 

[RoyVidar]

I would try to use wbk in stead of ActiveWorkbook, and I don't think you need all the properties

i e
[tt]wbk.SaveAs FileName:= _
"J:\cag\Network Information INFO,CSC_IVR\FISCAL 2008\New IBTM Report\Branch Transit Report\Report\Transit " & Forms!CTAS_BRANCH_REPORT!Text0 & ".xls"[/tt]

If that doesn't do the trick, try shortening the path/name to say c:\test.xls, and see if that helps. Do you really have a comma as part of a folder name?

Roy-Vidar

 

[math20]

Hi Roy,

Thank you. It worked fantastic. Yes actually there is a comma in the file name, I will remove it and see if it makes any difference. I inherited this folder from my colleague who used to work in this position.

Once again than you so much.

 

[math20]

Hi Again,

I ran into a problem while saving my file. Instead saving the new workbook (Book1) it is saving my excel template. I do not want to save my template I only want to save the new workbook that is created from this template.
Here is my code:

'Copy and move Excel Sheet to new workbook
wbk.Sheets("CTAS Total (2)").Copy ----This is where it creates a new workbook (Book1)from the template

wbk.Close ("CTAS BRANCH REPORT")---This is my Template

'Save Excel file into Report Folder
wbk.SaveAs FileName:= _
"J:\cag\Network Information INFO,CSC_IVR\FISCAL 2008\New IBTM Report\Branch Transit Report\Report\Transit " & Forms!CTAS_BRANCH_REPORT!Text0 & " June_06.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Thank you for your help.

 

[PHV]

Replace this:
wbk.Close ("CTAS BRANCH REPORT")
with this:
wbk.Close False
Set wbk = Nothing

And this:
wbk.SaveAs
with this:
ActiveWorkbook.SaveAs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[math20]

Hi PHV,

Thank you. It worked the first time but when I run the process second time for second transit it gives me the following error:

Run Time Error 91
Object variable or with block variable npt set.

This is where I get the error:
ActiveWorkbook.SaveAs FileName:= _
"J:\cag\Network Information INFO,CSC_IVR\FISCAL 2008\New IBTM Report\Branch Transit Report\Report\Transit " & Forms!CTAS_BRANCH_REPORT!Text0 & " June_06.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

I also shortened the file path to:
ctiveWorkbook.SaveAs FileName:= _
"C:\Branch CTAS Report By Transit\Transit " & Forms!CTAS_BRANCH_REPORT!Text0 & " June_06.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

It gives me the same error.

Thanks.

 

[PHV]

OOps, sorry for the typo:
[!]yourXLapp.[/!]ActiveWorkbook.SaveAs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[math20]

Hi PHV,

No problem.

When you say your XLapp you are refering to Book1, so that it will be:

Book1.ActiveWorkbook.SaveAs

Is this correct?

Thanks.

 

[PHV]

I don't know what Book1 is.
I meant the Excel.Application object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[math20]

Hi PHV,


Yeah, It Worked. Oh Thank you Thank you so much.

Have a Great day.

Thanks.