Saving Excel Spreadsheets (1 Viewer)

[Emohawk]

Hi Folks,
My first foray into Excel Spreadsheet creation has hit a snag.
I can create the spreadsheets and populate them with data as required however for some reason VBA does not recognise
.SaveAs as a valid action. Funnily enough even when I try to get a list of available actions from any Object type nothing comes up!
Any ideas as to what I'm doing wrong?

My code follows...

    Dim objXl, objWkb, objSht As Object
    Set objXl = CreateObject("Excel.Application")
    objXl.Visible = True
    Set objWkb = objXl.Workbooks.Add
    Set objSht = objWkb.Worksheets.Add
    
    Select Case ReportType
        Case "ODP"
            AddODPHeaders objSht
        Case "SDS"
            AddSDSHeaders objSht
        Case "SPV"
            AddSPVHeaders objSht
    End Select
    
    '.SaveAs is in capitals as VBA doesn't recognise it... ARGH!!!!
    objSht.SAVEAS = "G:\S.O.S\" & ReportType & ".xls"
    Set objXl = Nothing
    Set objWkb = Nothing
    Set objSht = Nothing

 

[Emohawk]

ARGH!!! HELP HELP HELP!!!!!!!!!!!!!!
By changing:

objSht.SAVEAS = "G:\S.O.S\" & ReportType & ".xls"
To
objSht.Application.Save

I can get a Save dialog box to appear in Excel. Now you'd think that using objSht.Application.SaveAs would work as well but noooooooo. It doesn't.

What the $%@* is happening?!?!?

I'm ready to take my PC and smash it into tiny pieces with a baseball bat then set fire to the remains with a flamethrower.

Save a PC, help me with this problem!!!

 

[CJBIRKIN]

Hello

try
objSht.SaveAs "C:\fred.xls"

Chris

 

[Emohawk]

Sorry I should have mentioned all the different combinations I've tried... The original code I posted contained an "=" for some reason...

lets see I've tried
objXl.Application.SaveAs "G:\S.O.S\" & ReportType & ".xls"
objSht.Application.SaveAs "G:\S.O.S\" & ReportType & ".xls"
objXl.SaveAs "G:\S.O.S\" & ReportType & ".xls"
objSht.SaveAs "G:\S.O.S\" & ReportType & ".xls"
objSht.Application.Save "G:\S.O.S\" & ReportType & ".xls"
The above one sort of works but still only brings up te dialog box...

I've tried creating the objSht as a ("Excel.Sheet") class instead of adding it as a Sheet to a Workbook but that brings it's own problems of when adding data it doesn't recognise the .Cells or .Range methods...

I've cut and pasted examples from my Help file but they seem to be error prone. I've made sure that the Excel Object Library is included in the Module references...

I'm losing my hair in great big clumps and there is a circular impression on my desk from repeatedly bangin my head.

I'm now resorting to trial and error but I feel like I'm trying to fit a key into a keyhole, in the dark, wearing oven mits, with a door 50 metres squre with a key made out of rubber.

Thanks for your help anyway... can you see my frustration?

 

[CJBIRKIN]

Hello

Thats odd as i pasted your code into a module removed the case statements and just created the workbook and it saved fine without the dialog appearing


Dim objXl, objWkb, objSht As Object
Set objXl = CreateObject("Excel.Application")
objXl.Visible = True
Set objWkb = objXl.Workbooks.Add
Set objSht = objWkb.Worksheets.Add

objSht.SaveAs "C:\fred.xls"

Set objXl = Nothing
Set objWkb = Nothing
Set objSht = Nothing

Have i got the wrong end of the stick again??!!

Chris

 

[Emohawk]

.................my stupidity amazes me sometimes......

Do you see those Sub calls to AddODPHeaders etc... Well their purpose is to add the header names of the Recordsets FieldNames... Now I thought I was being being good and at the end of these Methods I did this....

Set objSht = Nothing

I didn't realise that access passes the actual value when you make a call and not just a reference so I was dereferencing it before Access could save it.

bugger bugger bugger.

I removed the Set objSht = Nothing from the Subs and all works fine...

Thanks for your help, sorry for wasting you time.

Now I have to do something about my concussion...

Cheers

 

[CJBIRKIN]

Ah!
the fact that i didn't have those subs means my attempt worked. Oh well glad you solved it.

Is this the reason you had problems with

>I've tried creating the objSht as a ("Excel.Sheet") class instead of adding it as a Sheet to a Workbook but that brings it's own problems of when adding data it doesn't recognise the .Cells or .Range methods... <

if not try

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=33863


Chris

 

[Emohawk]

That actually was it! Thanks! I hadn't even begun to think about how I'd deal with all the extra pages in the workbook now...

Thanks for that, much appriciated. I dont suppose you know how to rename the Sheets do you?

 

[Emohawk]

Actually cancel that... I've worked it out.
Thanks for your help Chris you've opened my eyes to a whole new world of Access-Excel interaction.

 

[CJBIRKIN]

As you upsidedowners like to say No worries mate.

Chris

 

[Emohawk]

Well as you guys say...
Cheers Guv, you ain't arf bad for a northern monkey. If I catch you in me local boozer I'll shout you a pint of..... Fosters

Pete

 

[CJBIRKIN]

can i bring whippet t' pub?

 

[Emohawk]

Yeah and I'll bring Skippy...

I reckon we should lobby Jon for a chatroom...
Then again I'd never get anywork done..

 

[CJBIRKIN]

the watercooler?

 

[Emohawk]

I was thinking a proper chat room not so much a forum... It would be a great to learn about the people behind the Avatars...

Then again like I said I'd get nothing done and I if any valid topics were raised they wouldn't be stored in the forums.