Excel VBA losing changes with autosaving

[simon]

Hi,

I'm having a problem with Excel losing the changes I have been making
to a spreadsheet when selecting to close and SaveAs.

I have been dumping a report to an .xls file using the location:

Output_Err = ActiveWorkbook.Path & "\" & Supplier & "_Totals and
Error_" & TransDate & ".xls"

Using the following method:

Open Output_Err For Output As #2

and simply dumping using this kind of technique:

Print #2, HeadText_Gap
Print #2, HeadText_Rows
Print #2, HeadText_TotErr
Print #2, HeadText_Gap
Print #2, HeadText_Upload
Print #2, HeadText_Gap

Eventually the report is populated but is totally unformatted. So what
I have done is record a simple Macro to open up Output_Err, format it
and the close and save. I'm trying to do this without any popups
displaying so have a combo of:

Application.ScreenUpdating = False
Application.DisplayAlerts = False

in effect.

Everything runs through, formats, but when I open up the file to look
at it. All the changes are gone. Even the delimiting that is part of
the macro is not saved. I've tried things like this:

If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If

and

ActiveWorkbook.Close SaveChanges = True, Output_Err

but nothing seems to work.

When I leave the screenupdating and popups to display, I've noticed
that the filename appears in the save dialogue in quotemarks and the
file to save as is as a .txt even though the filename itself
mentions .xls. I'm guessing this COULD be the problem and have no idea
what to do if it is, but could also be in the wrong ballpark. It
appears that whatever save method I specify is totally ignored.

Any help would be great! Thanks!

Si

 

[jaf]

Hi Simon,
You can't write to a .xls file that way. You will corrupt the file.
Excel uses BIFF format. Do a google search for info on writing BIFF format.
It's not a simple task.

 

[simon]

Ok, will have a search! Cheers!

 

[George Nicholson]

Output writes to a text file. Even if you give it an xls extension, it will
still be a text file. If you subseqeuntly open that text file in excel and
add formatting, you need to specifically tell Excel to save the file as an
Excel file, or it will simply save in text format again & you will lose the
unsupported format-related changes.

HTH,

 

[simon]

I see what you're saying. Although I DID try a variant of my code
where I did use the option SaveAs.xlWorkbook (or something similar, I
forget the syntax) ... sadly no joy with that variety.