Another export to excel question

[PsYc0TiC]

Hi all,

I have been reading all over the net for 2 days and I am about as close as I can get without asking someone to help me with the remaining code to finish this off. I don't have any hair left to pull out.

ok... I have a bunch of queries I made and a form with buttons using macros to export data to excel with formatting and they work great individually. Too many buttons. I then embarked on the journey to combine all of this to one button using Vb code to run the individual queries and make one excel file with multiple sheets.

Once again this works. However... there is always a however isn't there... my OCD alarm keeps going off because my simple VB code does not export with formatting and the sheets look like crap without auto resize, headers color etc.



Below is my simple code... will someone please add in the code it needs to make it also preserve the formatting? Please please please?

Private Sub Command35_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "AdvanceWaitVis", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", True, "AdvanceWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ArcadiaWaitVis", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", True, "ArcadiaWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "EcruWaitVis", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", True, "EcruWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "LeesportWaitVis", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", True, "LeesportWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "RipleyWaitVis", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", True, "RipleyWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "WanekWaitVis", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", True, "WanekWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "WanvogWaitVis", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", True, "WanvogWaitVis"


End Sub

[June7]

You said macros worked. They exported with the formatting, etc.? Did you convert macros to VBA?

[PsYc0TiC]

You said macros worked. They exported with the formatting, etc.? Did you convert macros to VBA?

I don't know how to do that

[June7]

Bing: Access convert macros to VBA

Start with https://support.office.com/en-us/art...7-70649E33BE4F

Might find this site interesting http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

[PsYc0TiC]

Sigh..........

I don't see anything in the converted macro code that suggests anything about preserved formatting... unless this is it "acExportQualityPrint"

Might find this site interesting http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

read it... did not help

[June7]

And I never use macros. Provide macro details. Are you using ExportWithFormatting method?

[PsYc0TiC]

Im not using macros anymore as I stated in the 1st post... I posted the vba code I have now

[June7]

But you said the macros worked. So would be nice to analyze what works to see if and how can be translated to VBA.

[PsYc0TiC]

OK... so I did as you suggested and was able to create the output with the table formatting but the code doesn't work when I try to make it make multiple sheets.

Here is the converted macro code:

Private Sub Advance_Waiting_on_Visual_Report_Click()On Error GoTo Advance_Waiting_on_Visual_Report_Click_Err


    DoCmd.OutputTo acOutputQuery, "AdvanceWaitVis", "ExcelWorkbook(*.xlsx)", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", False, "", , acExportQualityPrint


Advance_Waiting_on_Visual_Report_Click_Exit:
    Exit Sub


Advance_Waiting_on_Visual_Report_Click_Err:
    MsgBox Error$
    Resume Advance_Waiting_on_Visual_Report_Click_Exit


End Sub

This won't let me define the different sheets like shown in the code in the first post. This code exports to a single worksheet workbook and the table formatting is correct. If I try to add in the sheet name after "FALSE" and before the "", , acExportQualityPrint" I get an error stating "too many arguments".

Here is what the string looks like that errors out (the text in red is causing it):

DoCmd.OutputTo acOutputQuery, "AdvanceWaitVis", "ExcelWorkbook(*.xlsx)", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", False, "AdvanceWaitVis" "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "ArcadiaWaitVis", "ExcelWorkbook(*.xlsx)", "W:\Quality-Projects\RCabler\Databases\Weekly Reports\Waiting on Visual Weekly Report.xlsx", False, "ArcadiaWaitVis" "", , acExportQualityPrint

[PsYc0TiC]

anybody? Please?

[PsYc0TiC]

Also trying to determine why this method will not export with formatting

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "AdvanceWaitVis", strFileName, True, "AdvanceWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ArcadiaWaitVis", strFileName, True, "ArcadiaWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "EcruWaitVis", strFileName, True, "EcruWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "LeesportWaitVis", strFileName, True, "LeesportWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "RipleyWaitVis", strFileName, True, "RipleyWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "WanekWaitVis", strFileName, True, "WanekWaitVis"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "WanvogWaitVis", strFileName, True, "WanvogWaitVis"

but this method will but I cannot add the part to name the worksheet and allow me to break to the next line and add another worksheet as in the code above.

DoCmd.OutputTo acOutputQuery, "AdvanceWaitVis", "ExcelWorkbook(*.xlsx)", strFileName, True, "AdvanceWaitVis", , acExportQualityPrint

[June7]

Sounds like first time I've encountered a situation that a macro does something VBA can't?

If macro does the job then have one button and run macro that executes the 7 exports.

[PsYc0TiC]

Sounds like first time I've encountered a situation that a macro does something VBA can't?

If macro does the job then have one button and run macro that executes the 7 exports.

It doesn't do the job... hence why I am asking for help. If you do not want to help me with this code just say so and move on please... this is going nowhere and is becoming even more frustrating.

[June7]

Did I misunderstand "using macros to export data to excel with formatting and they work great individually"?

I think your attempts have already discovered the limitations of TransferSpreadsheet and OutputTo methods.

If code (macro or VBA) cannot export with formatting and to multiple worksheets then the only alternative I know of is automation code - opening Excel objects in VBA and manipulating those objects to set formatting.

[ssanfu]

I use automation code - opening Excel objects in VBA and manipulating those objects to set formatting - I set the column widths, insert lines, add formulas, etc.
I haven't found any other way to format an Excel workbook from Access.


-------------------------------------------------------------------------------------------------------------------

It doesn't do the job... hence why I am asking for help. If you do not want to help me with this code just say so and move on please... this is going nowhere and is becoming even more frustrating.

I understand that you might be getting frustrated, but a response like this could/will seriously reduce your chances getting an answer.
There are several people that have lashed out like this (not to me) - I no longer respond to them. Continue like this response and you will probably be in the same situation.

Everyone in the forum is a volunteer giving of their time and knowledge. Being rude/disrespectful does not help your situation.......