Format & PageSetup changes are not saving with macro

[haedyr]

I have created a formatting macro, which would format what appears to be a .xls file, but is really a text(delimited) file. I'm basically wanting to change pagesetup and autofil the columns. When I run the macro, i can see the autofill changes taking place, but when I go to look at the saved file, the changes weren't saved. I have tried converting the file to .xls and then formating, or formating then converting to .xls to no avail. the code is below. Any ideas as to what I'm doing wrong?

Workbooks.Open Filename:=rawpathname & filenameextension & "\" & v(indx).name, ReadOnly:=False

Sheets(v(indx).name).Activate

Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With

ActiveWorkbook.Close savechanges:=True

thank you!

 

[SkipVought]

Hi,

How is your workbook saved? As a WORKBOOK or as a TEXT file.

Since you did not SaveAs a WORKBOOK, I suspect that you are loosing the formats.

Skip,

_[red][/red]

 

[haedyr]

Hi Skip,

that's where I think my problem is. here's the scenario:

if I right click on the original "workbook", properties show me it is a .xls file. However, when I open the file, make changes to the file, and then hit save, Excel tries to save it in .txt format. So, I believe the file is a .txt file, although the property shows it as .xls.

So, I worked around that. I had created another macro that would open the .txt file, format, and then save it as a .xls file. Unfortunately, that didn't work either.

I also attempted to just save the file as a .xls file and then run a separate macro to format, and that didn't work either.

I really do think it has to do with .txt vs. .xls, but I thought if I saved it as .xls, the changes would be saved.

ideas?

%-)

 

[AnotherHiggins]

Try this:

ActiveWorkbook.SaveAs "C:\Path_Here\FileName.xls", FileFormat:=xlNormal
Activeworkbook.Close

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[haedyr]

It worked!

I could kiss you right now!!!! Thank you sooooo much. Something as simple as that...go figure.

You rock the house.

Thanks again,

Haedyr

 

[AnotherHiggins]

Glad we could help!


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.