QueryDef and Export to Excel

[Sinfathisar]

So I had this code working and then I cleaned it up a little and it no longer works. It should export data from a created query using criteria selected by the user on a form and put it into an excel file that exists. I get no errors but it does not export anymore. After pouring over it for a while checking for mistakes with my form control references and variables I have yet to find anything. Any suggestions on what to look for or what might be missing? I did change my form name and edited the code accordingly after I already had it working, and changed a few form settings but changing them back did not fix the issue. I am not very experienced and stumped since I am not getting error messages.

Many thanks,

 'First set variables for the SQL string and CreateQueryDef command
 Dim [COLOR=red]strExport[/COLOR] As String
 Dim [COLOR=red]qdf[/COLOR] As dao.QueryDef
 
 'Then define the SQL to be exported (Static Response Info by ItemID)
 [COLOR=red]strExport [/COLOR]= "SELECT Static_Repeatability_Test_Table.[Static_Repeatability_ID], Static_Repeatability_Test_Table.[Static_Test_Item], Static_Repeatability_Test_Table.[Team_ID], Static_Repeatability_Test_Table.[Collection_Date], Seed_Test_Item_Table.[Static_Test_Item_Height], Static_Repeatability_Test_Table.[Static_Response_CH1], Static_Repeatability_Test_Table.[Static_Response_CH2], Static_Repeatability_Test_Table.[Static_Response_CH3], Static_Repeatability_Test_Table.[Static_Response_CH4], Seed_Test_Item_Table.[Response_Value_CH1], Seed_Test_Item_Table.[Response_Value_CH2], Seed_Test_Item_Table.[Response_Value_CH3], Seed_Test_Item_Table.[Response_Value_CH4], Static_Repeatability_Test_Table.[QCStatus_Ch1], Static_Repeatability_Test_Table.[QCStatus_Ch2], Static_Repeatability_Test_Table.[QCStatus_Ch3], Static_Repeatability_Test_Table.[QCStatus_Ch4] " & _
"FROM Static_Repeatability_Test_Table INNER JOIN Seed_Test_Item_Table ON Static_Repeatability_Test_Table.[Static_Test_Item] = Seed_Test_Item_Table.[Test_Item_ID] " & _
"WHERE [COLOR=red]Static_Repeatability_Test_Table.[Static_Test_Item] = Forms!Export_for_Excel_Chart_Static.SelectByItem [/COLOR]" & _
"ORDER BY Static_Repeatability_Test_Table.[Static_Test_Item], Static_Repeatability_Test_Table.[Team_ID], Static_Repeatability_Test_Table.[Collection_Date];"
 'Create the query using SQL defined above
Set [COLOR=red]qdf[/COLOR] = CurrentDb.CreateQueryDef("[COLOR=red]Static_Response_Export_" & Forms!Export_for_Excel_Chart_Static!SelectByItem, strExport[/COLOR])
  
 'Set variable for excel file to be exported into
Dim BookName As String
 'Get the workbook name
    [COLOR=red]BookName[/COLOR] = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\" & "Static_Response_Chart.xlsx"
'Export querydef into specified .xlsx file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "[COLOR=red]Static_Response_Export_" & Forms!Export_for_Excel_Chart_Static!SelectByItem[/COLOR], [COLOR=red]BookName[/COLOR], True
  
  'Clean up the objects created with this function
 [COLOR=red]qdf[/COLOR].Close
 Set [COLOR=red]qdf[/COLOR] = Nothing
 DoCmd.DeleteObject acQuery, "Static_Response_Export_" & Forms!Export_for_Excel_Chart_Static![COLOR=red]SelectByItem[/COLOR]

 

[JHB]

You are not showing the whole procedure/sub therefore I ask, do you have some error handling in your code, (if yes turn it of)?
Do the query get created, (CurrentDb.CreateQueryDef( ...))?

 

[Sinfathisar]

The code I posted is pretty much what I have, I just didn't include the "on click" and "end sub" parts. I don't have any error handling built in yet. I do not believe it is creating the query when I run it, though it did before I made changes. Copying the query into Access to check it seemed to work as it should.

Here is my code again:

 Private Sub ExportByItem_Click()
  
  'First set variables for the SQL string and CreateQueryDef command
Dim strExport As String
Dim qdf As dao.QueryDef

'Then define the SQL to be exported (Static Response Info by ItemID)
strExport = "SELECT Static_Repeatability_Test_Table.[Static_Repeatability_ID], Static_Repeatability_Test_Table.[Static_Test_Item], Static_Repeatability_Test_Table.[Team_ID], Static_Repeatability_Test_Table.[Collection_Date], Seed_Test_Item_Table.[Static_Test_Item_Height], Static_Repeatability_Test_Table.[Static_Response_CH1], Static_Repeatability_Test_Table.[Static_Response_CH2], Static_Repeatability_Test_Table.[Static_Response_CH3], Static_Repeatability_Test_Table.[Static_Response_CH4], Seed_Test_Item_Table.[Response_Value_CH1], Seed_Test_Item_Table.[Response_Value_CH2], Seed_Test_Item_Table.[Response_Value_CH3], Seed_Test_Item_Table.[Response_Value_CH4], Static_Repeatability_Test_Table.[QCStatus_Ch1], Static_Repeatability_Test_Table.[QCStatus_Ch2], Static_Repeatability_Test_Table.[QCStatus_Ch3], Static_Repeatability_Test_Table.[QCStatus_Ch4] " & _
"FROM Static_Repeatability_Test_Table INNER JOIN Seed_Test_Item_Table ON Static_Repeatability_Test_Table.[Static_Test_Item] = Seed_Test_Item_Table.[Test_Item_ID] " & _
"WHERE Static_Repeatability_Test_Table.[Static_Test_Item] = Forms!Export_for_Excel_Chart_Static.SelectByItem " & _
"ORDER BY Static_Repeatability_Test_Table.[Static_Test_Item], Static_Repeatability_Test_Table.[Team_ID], Static_Repeatability_Test_Table.[Collection_Date];"
'Create the query using SQL defined above
Set qdf = CurrentDb.CreateQueryDef("Static_Response_Export_" & Forms!Export_for_Excel_Chart_Static!SelectByItem, strExport)
  
'Set variable for excel file to be exported into
Dim BookName As String
'Get the workbook name
    BookName = DLookup("[projectpath]", "[Project_Defaults]") & "\Grapher\" & "Static_Response_Chart.xlsx"
'Export querydef into specified .xlsx file
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Static_Response_Export_" & Forms!Export_for_Excel_Chart_Static!SelectByItem, BookName, True
  
  'Clean up the objects created with this function
qdf.Close
Set qdf = Nothing
DoCmd.DeleteObject acQuery, "Static_Response_Export_" & Forms!Export_for_Excel_Chart_Static!SelectByItem
  
 'MsgBox ("Static_Response_Chart.xlsx Updated!")
 
End Sub

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=265778
To prevent a big copy/paste, please add the error code shown in my response to another Access ---> to Excel poster
Add your error trapping code, insure the Excel reference is set, then set a break point and step through your code.
In the code window be sure to use menu Debug - Compile

 

[Sinfathisar]

Thanks for the tip Rx - feeling really inexperienced here - can I set a start point for my code test? I have a bunch of other things that are not compiling in other modules. I would prefer to not have to delete all of that from my project. Setting my cursor in the correct code window does not seem to make it run from a specific point when I compile.

I have reference to Excel 15 turned on (but I am using Access 2007).

 

[Rx_]

Good question!
Now that you have the Immeidate Window open, it is very simple.

Lets say the sub name is MySub()
First set a break point in the code.
in the immediate window type MySub and return
If the code is in a form module, you would need to have the form open (in memory) so the sub would run.

If the Sub takes an argument MySuB(MyArg as integer)
In immediate window type MySub 3 then Return to run.

Functions return a value so we use the ? (shortcut for priint)
MyFunction(MyNumber as integer) as Integer
? MyFunction(5)

As you step through code, the error trap will help realize the exact spot the code jumped to errTrap.

B.T.W. lets say the code is suspended on a line of code.
You can hover your mouse over the variable and see the value.
Or, you can use the immediate window.
code example
MyString = "Abc" + MyForm!Mytextbox.value
If the line of code is stopped on this line, MyString will be empty.
After running this line, type in immediate window
? MyString (then return)


Stopping code and evaluating all the values is very powerful.

 

[Sinfathisar]

MySub() = ExportByItem_Click()
I have the form open.

I set a break at

 Set qdf = CurrentDb.CreateQueryDef("Static_Response_Export_" & Forms!Export_for_Excel_Chart_Static!SelectByItem, strExport)

When I put ExportByItem_Click in the immediate window and push return I get Compile Erro: Sub or Function not defined....

So I think I have a syntax error in the naming of my querydef. Tried to concatenate a static string with the value from my unbound combo box on the form... haven't figured out what it should be yet, but at least I know what the problem is! Thanks!