Help with macro

[ccl]

I am new to vba, not sure how to create codes or functions and how to execute them.

I am trying to create a macro or a function that will (at the click of a button in a form) export a query to excel with the file name and todays date.
I found this and created a module, but I don't know how to gwt it to work.
Dim MytPath, ExportFile as String

MyPath = "C:\MyFolder\"
ExportFile = Mypath & "export_" & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCombined_forexport", ExportFile



I changed the info for my file:

Dim MytPath, ExportFile as String

Mypath = "O:\dixon planning queries\reports\"
ExportFile = Mypath & "masterma" & Format(Date, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCombined_forexport", ExportFile

 

[Alansidman]

Create a command button on your form. In the on click event in the properties for the command button, put your code.

 

[noboffinme]

Hi ccl,

As Alansidman has advised, create the button on your Access Form & add this code after you change the Path where you want the file to save to & the query name.

[COLOR=black][FONT=Verdana]Option Explicit[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub cmd_qry_export_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim XLApp As Excel.Application[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim XLwkbk As Excel.Workbook[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim a As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set XLApp = New Excel.Application[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]a = "qry_closed_projects"                                   ' Replace with your query name[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    DoCmd.OutputTo acOutputQuery, a, acFormatXLS, "C:\Users\qry_closed_projects.xls", True              ' Replace the filepath[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Set XLwkbk = XLApp.Workbooks.Open("C:\Users\qry_closed_projects.xls")[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    XLwkbk.Sheets(1).Select[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]With ActiveSheet[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Rows("1:2").Select[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Range("A1") = a[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Range("A2") = Date[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

 

[ccl]

Where do I put the code when I create the button.
Can I use a macro then create a button from the macro.

 

[pr2-eugin]

Buttons are Form entities.. They cannot be created by Macros.. You can Code the Macro to perform certain action.. What noboffline meant was.. Create a button on the Form in Design mode.. Then name the button cmd_qry_export then..

1. Set the On Click property of the button to [Event Procedure].
2. Click the Build button (...) beside this. Access opens the Code window.
3. Enter this line between the Private Sub... and End Sub lines:

 

[ccl]

Sorry to be such an idiot on this but I keep getting an error message.

this is how I have it:

Private Sub Toggle20_Click()
Private Sub cmd_qry_export_Click()
Dim XLApp As Excel.Application
Dim XLwkbk As Excel.Workbook
Dim a As String
Set XLApp = New Excel.Application
a = "masterma"
DoCmd.OutputTo acOutputQuery, a, acFormatXLS, "O:\Dixon Planning Queries\masterma by date\masterma.xls", True
Set XLwkbk = XLApp.Workbooks.Open("C:\Users\qry_closed_projects.xls")
XLwkbk.Sheets(1).Select
With ActiveSheet
Rows("1:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = a
Range("A2") = Date
End With
End Sub

 

[ccl]

I think i'm getting closer. I am getting a "compile error" message
"User dfined type not defined" with this line "highlighted Dim XLApp As Excel.Application"

Option Explicit
Private Sub cmd_qry_export_Click()
Dim XLApp As Excel.Application
Dim XLwkbk As Excel.Workbook
Dim a As String
Set XLApp = New Excel.Application
a = "masterma"
DoCmd.OutputTo acOutputQuery, a, acFormatXLS, "O:\Dixon Planning Queries\masterma by date\masterma.xls", True
Set XLwkbk = XLApp.Workbooks.Open("C:\Users\qry_closed_projects.xls")
XLwkbk.Sheets(1).Select
With ActiveSheet
Rows("1:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = a
Range("A2") = Date
End With
End Sub

 

[pr2-eugin]

Make sure you add Microsoft Excel XX.X Object Library under Tools-> Reference.. Where XX.X represents the version..

PS: Please use Code Tags when posting VBA Code

 

[noboffinme]

Hi ccl,

It may help if I explain why you need to do these things to get Excel to work from Access.

Access doesn't normally understand 'Excel' type commands. In order to help, you need to let Access 'access' the Excel Reference Library by going to Tools >> References as pr2-eugin advised & check the checkbox for the version of Microsoft Excel you have.

I'm using Access 2013 & I've attached a screenshot below of the Ref Library window to show you what you're looking for. NOTE In 2013, the Microsoft Excel Ref Library version is 15, yours may be 12 or some other so select that one & retry the code.