Formatting of output tables to excel

[hinkwale]

Hello there!


The first time I ran the application I’m getting the selected output exported to excel in one workbook with partial formatting and here’s the vba portion for formatting:
Set rng = .range("A1", sh.Cells(100, LastColumn).End(xlUp))
rng.WrapText = True
End With
With sh
.Rows("1:1").Font.Bold = True
.Rows("1:1").Font.Name = "celibri"
.Rows("1:1").Font.Size = 11
.Rows("1:1").Interior.Color = RGB(192, 192, 192)
.Rows("1:1").ColumnWidth = 50 'only for these headers in the E2E Finding and Recommendations, Open and Closed tables (DescriptionOfFinding, Owner, Recommendation, FindingType, BusinessResponse, E2E CommentsBusinessResponsesAndfurtherActions) and Finding Supplier Commentary header in CID Template and Auto fit for the rest of the fields
.Rows.RowHeight = 315 'only for the records/rows in both the CID Template and E2E Finding and Recommendations tables

The only formatting done is the wrapping of text and bold headers when the application is ran for the first time and when I try to rerun as a test it looks like the excel file doesn’t get closed after formatting and so deleting and replacing it with another output ran for another date creates the following error (object variable or With block variable not set) and that the output file is still open.

If you ran the app twice you’ll see what I’m saying. The app is supposed to create excel objects, open then format followed by save and closed. It doesn’t look like all the formatting is getting done and it doesn’t close the excel file when done. Its supposed to delete output already in the location and replace with the new one. Running it over creates message saying output is already open.
The E2E_F&R_Output_Files excel attached is exactly how I want the output tables to be formatted in excel.
Can someone help with the vba in the database attached to correct this for me? The vba for this is in the Form_frmExport form.

Thanks

[NTC]

so here is my 2 cents - which is probably not going to be what you want to hear; but formatting data going "to excel" is not the same thing as formatting the layout "in excel"......and what you are attempting to do is format the excel sheet itself rather than the Access data going to excel. It may seem like hair splitting at first but there is a big difference. Formatting an excel sheet's layout from the Access side is asking a lot and in general is not going to be within the Access programming skill set so this really isn't the ideal forum. What you want to do is control the formatting of the excel sheet in excel - possibly triggered in some suitable trigger point, or even potentially a user button.... or you can consider setting up a template that is pre-formatted. I'm not saying the approach that you are taking is not possible - but it isn't really Access development.

[June7]

Isn't the font name "Calibri" - with an 'a'?

Suggest you set the form Popup and Modal to No while debugging. Also, comment out the On Error GoTo until the procedure works. I prefer Overlapping Windows to Tabbed Documents.

The export works the first time after opening the db. Even after manually clearing the process in Task Manager and deleting the workbook, I then get error "The remote server machine does not exist or is unavailable". This is triggered on the line: With ActiveWindow. So I commented the lines dealing with ActiveWindow and errors on If Not ActiveSheet.AutoFilterMode. I changed ActiveSheet to sh and export works. The Excel process clears from TaskManager.

I don't know how to fix ActiveWindow.

You might want code that checks if file already exists before exporting.