Export query to Excel in "custom" folder 1

[benredik]

Hello,

I have a query that I would like to export as a Excel file.

One of the fields in the query is a "keydate" (mmyy), with a value that is the same for all records in the query, that I would like to export to a folder called the keydate (mmyy).

example: C:\database\opex\0406\DirectOpex.xls

Here the 0406 would be the keydate. I would like to to this as simple as possible. I would appreciate any help or ideas, but please explain carefully as I'm not a very experienced "developer".

Thank you in advance.

Peter

 

[VicRauch]

Peter,
I assume you have access within your code to the "keydate" so here is sample code that works. All you need to do is change the reference to [highlight]Forms!Form2!keydate[/highlight] to however you can reference "keydate" in your code. Please note, it is used twice in the code.

Const OpexPath As String = "C:\database\opex\"

[green]'1. Create folder if needed[/green]
On Error GoTo ErrorRoutine
  MkDir OpexPath & Format(Forms!form2!keydate, "mmyy")
  
[green]'2. Export query into keydate folder[/green]
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryDirectOpex", _
                OpexPath & Format(Forms!form2!keydate, "mmyy") & "\" & "DirectOpex.xls"
  
  Exit Sub
  
ErrorRoutine:
  If Err = 75 Then Resume Next      [green]'Handle Path/File _
                                     error because the _
                                     keydate folder is _
                                     already there[/green]
  Debug.Print Err, Err.Description
  Exit Sub

 

[benredik]

Thank you for the help VicRauch!

It worked perfectly (except I had to change the acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel97 as I have Access 97).

Thanks again.