Run-time error 1004 while Export to Excel

[Heidestrand]

Hello,

I'm using Access 2010 and I want to export a query to Excel by clicking a button. Every time I click on it Excel opens but doesn't open my worksheet, it's just gray like you just open the program without a table. After exiting it Access tells me I got a run time error 1004: the open-method of the workbook object couldn't be executed. After opening my Excel file it says that Excel found unreadable content and asks if I want to restore the content of the workbook.
Here is my code:

Private Sub Befehl62_Click()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef, SheetName As String, xlApp As Object
    Set cdb = CurrentDb
    Forms!Export_to_Excel!txtSheetName.SetFocus
    SheetName = Me!txtSheetName.Text

    Const xlsxPath = "\testchart.xlsx"

    ' create .xlsx file if it doesn't already exist, and add the first worksheet
    Set qdf = cdb.CreateQueryDef("'" & SheetName & "'", _
            "SELECT * FROM tblDetector")
    Set qdf = Nothing
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "'" & SheetName & "'", xlsxPath, True
    DoCmd.DeleteObject acQuery, "'" & SheetName & "'"
 
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
   
    xlApp.Workbooks.Open "\testchart.xlsx", True
    Debug.Print xlApp.Version
    Set xlApp = Nothing
End Sub

Does someone know where my problem is?

Best regards,

Heide

 

[gemma-the-husky]

I am not sure all the code is 100% anyway

I would

1) add some proper error handling to your code.

2) suppress this until it is all working.
DoCmd.DeleteObject acQuery, "'" & SheetName & "'"

3) xlApp.Workbooks.Open "\testchart.xlsx", True
clarify which folder this is actually using (assuming the command is correct)

 

[Heidestrand]

@gemma-the-husky:

Thank you for your answer!

My code is complete, I didn't delete anything before posting (only edited the path to the Excel file).

Can you advise me how to add proper error handling to my code? I would use the Debug.Print method. But which variable should I print out?

I suppressed it, now it keeps the new created query.

The Excel file is stored on a network server, I only deleted the first part of the path. The full path is working, I already tested it

What I can add is that the content or information of my query are completely exported to the Excel sheet but every time I open the file manually it says there is unreadable content that needs to be restored. So the transfer isn't working correctly.

 

[Minty]

I would try simply creating it first NOT opening it.
I suspect your problem is that Access is keeping the Excel application open, when it thinks it is already open...

 

[vbaInet]

Try

Private Sub Befehl62_Click()
    Dim xlApp    As Object
    Dim cdb      As DAO.Database
    Dim strQuery As String
    
    Const xlsxPath = "[COLOR="Red"]C:\Proper\Path\To[/COLOR]\testchart.xlsx"
    
    Set cdb = CurrentDb
    
    strQuery = Me!txtSheetName.Value
    
    ' Edit the SQL of the querydef if necessary
    cdb.QueryDefs(strQuery).SQL = "SELECT * FROM tblDetector;"
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQuery, xlsxPath, True
    
    DoEvents
    
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Workbooks.Open xlsxPath
        .Visible = True
        .UserControl = True
        Debug.Print .Version
    End With
    Set xlApp = Nothing
End Sub

 

[Heidestrand]

@vbaInet: Thank you for your code! Unfortunately it gives me a run time error 3265: couldn't find element in this list. And it's pointing at the line cdb.QueryDefs(strQuery).sql = "SELECT * FROM tblDetector;"

Any ideas what's wrong with it?

 

[gemma-the-husky]

the above code misses out the "createquerydef" section, which is still needed. Is your version an edited version, or the full version?

 

[spikepl]

When you have a statement giving a run-time error then the first thing to check is whether the bits you pour into it are OK. In your case strQuery

 

[vbaInet]

It is intended that you don't create and delete the querydef at runtime, but rather change the SQL of an existing querydef . See the comment.
So create the query and the code will run.

 

[Heidestrand]

Thanks a lot for all your answers! So I edited my code it now it almost works how I want:

Private Sub Befehl62_Click()
On Error GoTo ErrorHandling

     Dim xlApp As Object
     Dim strQuery As String
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     
     Const xlsxPath = "\\Path\Mappe1.xlsx"
     
     strQuery = Me!txtSheetName.Value

     Set db = CurrentDb
     Set qdf = db.CreateQueryDef

     With qdf
         .Name = "" & strQuery & ""
         .sql = "SELECT * FROM tblSysSold"

     End With
     db.QueryDefs.Append qdf

     RefreshDatabaseWindow
     
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQuery, xlsxPath, True
     
     DoEvents
     Set xlApp = CreateObject("Excel.Application")
     With xlApp
         .Workbooks.Open xlsxPath
         .Visible = True
         .UserControl = True
         Debug.Print .Version
     End With
     Set xlApp = Nothing

ErrorHandling:
    Select Case Err.number
      Case 3265
        Resume Next
      Case Else
        MsgBox "Fehler " & Err.number & vbCrLf & Err.Description
        Resume Next
    End Select

     DoCmd.DeleteObject acQuery, "" & strQuery & ""
     Set db = Nothing
     Set qdf = Nothing
End Sub

I had a problem with an object that wasn't listed after creation. So I used the RefreshDatabaseWindow function to refresh the list and make the export possible. But there is one tiny thing: when I type the name of my query into the textbox (like "test") then Excel is creating a worksheet with an underscore: "_test"

How can I prevent this? And how can I open the worksheet in Excel I created?

 

[vbaInet]

[COLOR="red"]     Set qdf = db.CreateQueryDef[/COLOR]

     With qdf
         .Name = "" & strQuery & ""
[COLOR="red"]         .sql = "SELECT * FROM tblSysSold"[/COLOR]

     End With
[COLOR="Red"]     db.QueryDefs.Append qdf

     RefreshDatabaseWindow[/COLOR]

All of this don't work together. Please refer to my posts for the correct structure. I mentioned that you should manually create the query and if necessary alter the SQL of the query. If the SQL of query never changes, then you don't even need to alter the SQL, just create the query manually and remove all the other "fluff" in your code.

 

[Heidestrand]

@vbaInet:
If I understood you correctly then you mean that I have to manually create strQuery as a new query and alter the SQL code inside it, right?

Honestly, this isn't a bad idea at all I have SQL code that changes every time but I can save the code in that specific strQuery query and perform your export code. But for this I don't need
cdb.QueryDefs(strQuery).sql = "SELECT * FROM tblDetector;"
because I alter the SQL code manually with vba.
I'm going to remove the fluff from my code and only alter the query every time. So your export code can stay how it is

 

[vbaInet]

Yes that's what I'm saying

Honestly, this isn't a bad idea at all I have SQL code that changes every time but I can save the code in that specific strQuery query and perform your export code. But for this I don't need
cdb.QueryDefs(strQuery).sql = "SELECT * FROM tblDetector;"
because I alter the SQL code manually with vba.

... but you might be missing the point here. If the SQL changes often and you build the SQL string using VBA, then you need that line of code above to append the new SQL to the existing querydef. Think of that query as your template.

The structure explained is what was given in my first post.

 

[Heidestrand]

@vbaInet:
I think I didn't really express myself correctly

The DoCmd.TransferSpreadsheet acExport function only accesses the query with the name strQuery, but it doesn't care how the query was created. So when I leave your code inside a normal private sub and have another sub that is public, generates my SQL code depending on other factors and saves the SQL string inside the strQuery query then I don't need the line cdb.QueryDefs(strQuery).sql = "SELECT * FROM tblDetector;" inside the export procedure

But please correct me when I'm wrong or didn't get the point. I'm new in vba

 

[vbaInet]

Makes sense, you simply moved that line to your other Sub. Good job!