probs with saving in Excel using VBA

[cerebros]

Hi everyone,

Just a quick note before I start – I created the main database I’m having problems with about 2 years ago now and haven’t had to do anything with VBA since so I’m having to relearn what half of what’s in my DB is doing.

Anyway, my company has just upgraded the internal network to bring everyone onto a new domain following a merger last year. This means that as well as running Windows XP desktops now as opposed to Citrix thin clients, we’ve also gone up from Office 2000 to Office 2003.

I’ve got a small handful of databases I’ve set up for my department which appear to have been messed up by this change. For reasons I won’t go into here, I need to export my reports as HTML, open them in Excel, make some page layout and formatting adjustments and then save the file as .xls and delete the intermediate HTML file.

This has all been working from my main database (which the other databases borrow the relevant code from) for pretty close to 2 years, until this change was made.

Now I’m getting an error message when I try and extract my reports. The code below is used to save the file and delete the intermediate HTML file.

Public Sub SaveAndTidyUp(oSheet As Excel.Worksheet, oWB As Excel.Workbook, oXL As Excel.Application, runreports As String, FilePath As String)[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Dim fs, f[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Set fs = CreateObject("Scripting.FileSystemObject")[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]         'Alter the runreports value to give ourselves the filename to save the report as[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         runreports = Left(runreports, InStr(runreports, ".html") - 1) & ".xls"[/FONT][/COLOR]
 
 
[COLOR=black][FONT=Verdana]         'Save the report[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         If Dir(FilePath & "\" & runreports) = "" Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]             Set f = fs.getfile(FilePath & "\" & runreports)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]             f.Attributes = f.Attributes - 1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]             'Run kill to delete any earlier versions from today[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]              Kill FilePath & "\" & runreports[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         'then savethe report proper[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         [COLOR=red]oSheet.SaveAs FilePath & "\" & runreports, xlNormal[/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'            [COLOR=blue]ActiveWorkbook.SaveAs FileName:=FilePath & "\" & runreports, FileFormat:=xlNormal[/COLOR][/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]         'close the spreadsheet and quit Excel[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         oWB.close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         oXL.Application.Quit[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]         Set f = fs.getfile(FilePath & "\" & runreports)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         f.Attributes = f.Attributes + 1[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]         'Alter the runreports value back to its original contents and then remove the HTML file[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         'that we created the spreadsheet from in order to keep the drives tidy[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         runreports = Left(runreports, InStr(runreports, ".xls") - 1) & ".html"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Kill FilePath & "\" & runreports[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

The error I get occurs in the line highlighted in red and is as follows:

Run-time error ‘1004’: Method ‘SaveAs’ of object ‘_Worksheet’ failed

I’ve tried searching the ‘net for answers but can’t seem to find anything relevant to what I’m doing.

If I try using the line highlighted in blue instead then the first report I try will run but not subsequent reports. With this I get the following message:

Run-time error ‘91’: Object variable or With block variable not set

Again I’ve googled this but can’t seem to find any answers relating to my situation.

Can anyone help?

thanks

 

[chergh]

oSheet.SaveAs FilePath & "\" & runreports, 1

 

[DCrake]

As Chergh supplied the solution, I will just explain one little thing. using the xlNormal constant is private to Excel. by using its numeric equivelant disambiguates between Access and Excel.

 

[cerebros]

Cheers Chergh, works a treat.


DCrake - I assume that's a change in 2003 then since using xlNormal worked up until now?