VBA & Excel: macro wont save column adjustments

[jacjacjac]

Hello,

My Aim: i wish to place text in one sheet and adjust columns in another sheet using similar code or the code below

Problem: It runs the Sub however, it executes the code to place the text 'XXX' in the correct spot, however it wont adjust the columns. Once i open
the file up the text is there but columns haven't changed.
I figured the code is similar so why is it executing and saving the text but not the column widths.

I think it might be that the column manipulation needs to be saved? If so, howr? If not any ideas?

(also this Sub is called from a macro that runs over an Excel file, that (the macro) is called from a form in Access)

Sample code

[Private Sub editCols()

Dim frontSht As Worksheet
Set frontSht = Sheets("Front_sheet")

frontSht.Range("H2").Value = "XXX"

Dim backSht As Worksheet

Set backSht = Sheets("Full_Revision_History")

backSht.Columns("A:Q").AutoFit
backSht.Range("A:P").HorizontalAlignment = xlCenter

End Sub]

Thanks,

[Eri523]

Works for me (Excel 2003, macro stored under ThisWorksheet, not invoked from Access though). Perhaps some other part of your code that you didn't show simply doesn't properly save the workbook after making the changes?

Your code tags are wrong, BTW.

[jacjacjac]

Yeah it does work when i run it inside the Excel file and when you turn Excel displays on, you can see if autofit columns however once its run from the macros the Sub runs but the columns dont change. Im guessing the Sub is running and the columns are cahnging however the file isn't saved and it appears that the changes didn't happen....however if this was the case why does the text remain??

[Eri523]

My suspicion rather is that Excel, started invisibly from Access, somehow isn't able to perform the AutoFit, since certain GUI objects needed for that didn't get created that way.

However, this, run from another Excel workbook, does not reproduce the problem:

Sub TestInvisible()
  Application.Visible = False
  Application.Workbooks.Open "t523105.xls"
  Application.Run "t523105.xls!ThisWorkbook.editCols"
  ActiveWorkbook.SaveAs "t523105 saved.xls"
  ActiveWorkbook.Close
  Application.Visible = True
End Sub

Unfortunately, I'm comparatively bad at Access VBA and it's quite late here right now. Can you post a small Access macro, much like the editCols from the initial post (and probably calling that), that reproduces the problem?

Or did you actually ever try to run your production code from Access, just starting Excel visibly? If that worked it would strengthen my suspicion.