Convert "OutputTo" to vba code

  • Thread starter Thread starter PJLaux
  • Start date Start date
P

PJLaux

I have an access macro which, in the end, creates a table where I use
the "Output" to Action which opens excel where the user is required to
save to a path "Y:\PAYR\Taxes" and to save it as the name that they
want to save it as. Apparently, that is not robust enough, so I need
to create some vba code to save the table in this same path and I
would be creating the name so that it combines several of the combo
and text boxes from the open form, "frm_main" along with the current
date. For example, the name would be something like this.
=Forms![frm_main][Combo_Qu_Or_YTD]&" "&Forms![frm_main][Txt_Year]&"
"&Forms![frm_main][txt_company]&" "&Forms![frm_main][Txt_Year]&"
"&Date().
Can someone please help me with creating the code to run this in a
subprocedure.
Also, is there a way to have an excel macro that automatically
executes some excel vba code, that I created for this spreadsheet, as
soon as this spreadsheet is created or would each user need to have a
specific macro icon set up on their workstation which they can then
activate once this spreadsheet is created.
 
I presume you'll need the TransferSpeadsheet method for this. If you include the sub in the actual frm_main module itself rather than a seperate module you can avoid using all the Forms![frm_main] stuff too

Code: 
Dim strFileNameAndPath as string
strFileNameAndPath = "Y:\PAYR\Taxes\" & Combo_Qu_Or_YTD & " " & Txt_Year & " " & txt_company & " " & Txt_Year & " " & Date() & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tblTheTable", strFileNameAndPath, True
Any good to you?
 
Back
Top