Formatting Excel spreadsheet in Access (1 Viewer)

[fredalina]

I have transfer spreadsheet code that creates an Excel workbook with 4 tabs (Tab1, Tab2, Tab3, Tab4). i then want to format the worksheets where the first row of each is bold and all columns are autofit, however Tab1 should have columns C and beyond be formatted with two decimal places, and Tab3 should have columns C and beyond be formatted as currency (if this doesn't work, two decimal places will be acceptable).

            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Tab1", "[URL="file://\\nmchqt59\nmc"]Path[/URL]"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Tab2", "[URL="file://\\nmchqt59\nmc"]Path[/URL]"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Tab3", "[URL="file://\\nmchqt59\nmc"]Path[/URL]"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Tab4", "[URL="file://\\nmchqt59\nmc"]Path[/URL]"
  
 
strWkbkName = "[URL="file://\\nmchqt59\nmc"]Path[/URL]ls"
    
     strWkSt = "Tab1"
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (strWkbkName)
    With objXL
        .Worksheets(strWkSt) _
        .Rows("1:1").Font.Bold = True
        .Columns("A:Z").Autofit
        .Rows("5:99").NumberFormat = "0.00"
        .Save
        .Workbooks.Close
    End With
    
    strWkSt = "Tab2"
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (strWkbkName)
    With objXL
        .Worksheets(strWkSt) _
        .Rows("1:1").Font.Bold = True
        .Columns("A:Z").Autofit
        .Save
        .Workbooks.Close
    End With
    
    strWkSt = "Tab3"
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (strWkbkName)
    With objXL
        .Worksheets(strWkSt) _
        .Rows("1:1").Font.Bold = True
        .Columns("A:Z").Autofit
        .Save
        .Workbooks.Close
    End With
    
    strWkSt = "Tab4"
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (strWkbkName)
    With objXL
        .Worksheets(strWkSt) _
        .Rows("1:1").Font.Bold = True
        .Columns("A:Z").Autofit
        .Rows("2:99").NumberFormat = "0.00"
        .Save
        .Workbooks.Close
    End With

Tab1 comes out perfectly. The cells have two decimal places and everything is formatted wonderfully. Tab2, Tab3, and Tab4 are all formatted as far as the first row being bold, but the columns are not Autofit and Tab3 does not have the decimal place formatting. I've tried both .NumberFormat = "0.00" and .NumberFormat = "$0.00". When I use the latter, Tab1 is formatted with the $ sign instead of Tab3. I even went so far as swapping the order in the code between Tab1 and Tab3, but Tab1 is still correctly formatted and Tab3 still has too many decimal places and no dollar sign.

Also, when it gets to each .Save point, it states that RESUME.XLW already exists and asks if the user would like to overwrite it (if the code has been previously run). Is there a way to make this save automatically so the user doesn't have to click Yes?

Thanks!

 

[namliam]

Well to start with...

     strWkSt = "Tab1"
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (strWkbkName)
    With objXL
        .Save
        .Workbooks.Close
    End With
    
    strWkSt = "Tab2"
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (strWkbkName)

Why create seperate and new Excel instance? And why save and close all the time?? Why not simply keep it open?? Saves time and coding...


Secondly... your problem

    With objXL
[B]        .Worksheets(strWkSt) _[/B]
        .Rows("1:1").Font.Bold = True
        .Columns("A:Z").Autofit
        .Rows("5:99").NumberFormat = "0.00"
        .Save
        .Workbooks.Close
    End With

Notice the bolded line... Notice the "_" at the end, which indicate a line continuation....

A small re-write of your code would be:

    With objXL
        .Worksheets(strWkSt).Rows("1:1").Font.Bold = True
        .Columns("A:Z").Autofit
        .Rows("5:99").NumberFormat = "0.00"
        .Save
        .Workbooks.Close
    End With

Which might show you your problem more obviously... You are only executing the bold on the designated spreadsheet, the rest is executed on the (more or less by accident) open tab.... which usually will be tab1 if you just created the file.

You either have to ".Select" (or was it .Active?, I forget) the sheet/tab or use "With" on the Worksheet line, both without the continuation.

Dont know what to do about "Resume.xlw" I have never dealt with that... Perhaps it has to do with your not closing the application properly...
No where in your code do I see a "objXL.Quit"
and/or "set objXL = nothing"

Hope this helps

 

[fredalina]

The reason why I saved and closed each step is, I believe, related to the RESUME.XLW issue. I am accessing the database through a Citrix server by necessity (nearly all of our reporting databases go through Citrix for speed and consistency), and for some reason Citrix is holding onto the spreadsheet until after everything has run.

If I change the code to your suggestion (thank you, by the way), and remove the .Save and .Close from each step, adding in the following at the end:

    DoCmd.SetWarnings True
    objXL.DisplayAlerts = True
    objXL.Save
    objXL.Workbooks.Close
    objXL.Quit
    Set objXL = Nothing

I still get the RESUME.XLW popup, and when I try to open the spreadsheet, it says it is locked by another user. When I go to close completely out of Citrix, it prompts me "Do you want to save?" When I select Yes, it tries to save a copy instead of overwriting the original.

 

[fredalina]

It won't let me edit out the previous post. I got everything working with your code perfectly. Only issue is it still pops up the RESUME.XLW once per run.

Thanks!

 

[namliam]

what happens to excel thru the Citrix when you manually work it??

Do you have another excel session open?

I am guessing it has something to do with the Citrix, but I am not sure... I have never had this happen.

 

[wiklendt]

as for number formatting, this works for me:

.NumberFormat = "$#,##0.00"