[SOLVED] Changing fileformat when saving?

stildawn · Sep 18th 2013

Hi All

This is one area where I have always had issues... I want to save a copy of a sheet using this code:

Code

Sheets("Air").Copy
Sheets("Air").SaveAs Filename:="N:\National Share Drive\1 CSU Homepage\CS Interactive Reports\Formatted Reports\" & strClientName & "-Air-" & Format(Date, "dd.mm.yy") & ".xls", FileFormat:=xlXMLSpreadsheet
Workbooks(strClientName & "-Air-" & Format(Date, "dd.mm.yy") & ".xls").Close

This works, it saves only the "Air" sheet as a new workbook, but when ever anyone opens the new file it comes up with the "the fileformat does not match the extension blah blah blah"

I'm after just a standard .xls file, so that none of my code is saved with the report, can someone in the know about this direct me as to how to save just the standard no macros .xls workbook??

Thanks

stildawn · Sep 18th 2013

Re: Changing fileformat when saving?

Also just noticed that none of the inserted pictures are being saved with the new workbook??

Is there a way to make sure they save with workbook?

HaHoBe · Sep 18th 2013

Re: Changing fileformat when saving?

Hi, stildawn,

maybe like this:

Code

Sheets("Air").Copy
ActiveWorkbook.SaveAs Filename:="N:\National Share Drive\1 CSU Homepage\CS Interactive Reports\Formatted Reports\" & strClientName & "-Air-" & Format(Date, "dd.mm.yy") & ".xls", FileFormat:=xlExcel8
ActiveWorkbook.Close False

Maybe consider to use the formatting like yy_mm_dd instead of the format you used.

Ciao,
Holger

stildawn · Sep 18th 2013

Re: Changing fileformat when saving?

Thanks Holger

That worked perfectly (including the pictures) after I added some Application.DisplayAlerts = False/True codes around it. It said some of my conditional formatting was not going to work but I checked and it was perfect so meh?

Cheers

HaHoBe · Sep 18th 2013

Re: Changing fileformat when saving?

Hi, stildawn,

if your actual workbook is in a newer format CF will be cut down to the limitations for workbooks in xls-Format (only 3 conditions) as well as other restrictions reagrding formulas, pivots, and more. As far as I know you can turn the checking off in the workbook you want to save.

HTH,
Holger

stildawn · Sep 18th 2013

Re: Changing fileformat when saving?

I only have one conditional formatting, which highlights blank cells in a certain range, it seems to be working fine so its not an issue.

Participate now!

Share