Please don't flame me for this but I have been reading and trying different code combinations 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 as I can't seem to know enough to edit the codes I have found.
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.
I am needing the output file to output to the workbook preserving the original formatting and then perform conditional formatting in the Excel sheets on column F that checks to see if the date is 14 days old or older and if so fill the cell red, if not no cell color. I know how to do the conditional formatting in the Excel file but since my code only creates a new file with no formatting I assume I need to use a template and just overwrite the data that goes into the individual sheets each time I click the button on my form to generate the export but I don't know how to code that and everything I have tried isn't working.
I have tried and tried but I just keep failing to accomplish this.
Below is my simple code... will someone please add in the code it needs to make this all happen? Please please please?
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.
I am needing the output file to output to the workbook preserving the original formatting and then perform conditional formatting in the Excel sheets on column F that checks to see if the date is 14 days old or older and if so fill the cell red, if not no cell color. I know how to do the conditional formatting in the Excel file but since my code only creates a new file with no formatting I assume I need to use a template and just overwrite the data that goes into the individual sheets each time I click the button on my form to generate the export but I don't know how to code that and everything I have tried isn't working.
I have tried and tried but I just keep failing to accomplish this.
Below is my simple code... will someone please add in the code it needs to make this all happen? Please please please?
Code:
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