Solved Cannot see the wood for the trees :(

[Gasman]

Hi all,
I am trying to export query data to an Excel workbook and sheet.

I have the following code created by a respected member of the MS community. Copied and amended for my situation.
It was late binding as you can see, but I changed it to early just to see if that was the issue.
I have MS Excel 16 in my references.

I get the error when trying to open the workbook which does exist in that path.
I have tried the app with New as well as without.
So what am I missing please.?
ApXL is created and visible in Locals on both syntax.

Function SendToExcel(strTQName As String, strSheetName As String, strPath As String)

' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strPath is full path and name of Excel workbook

    'Dim ApXL As Object, xlWBk As Object, xlWSh As Object
    Dim ApXL As Excel.Application
    Dim xlWBk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim rs As DAO.Recordset

    On Error GoTo Err_Handler
    Debug.Print "strTQname: " & strTQName
    Debug.Print "strSheetName: " & strSheetName
    Debug.Print "strPath: " & strPath

    Set rs = CurrentDb.OpenRecordset(strTQName)

    Set ApXL = New Excel.Application


    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True



    Set xlWsh = xlWBk.Worksheets(strSheetName)

    rs.MoveFirst

    xlWsh.Range("A2").CopyFromRecordset rs

    'Selects the first cell to unselect all cells

    xlWsh.Range("A2").Select



    xlWsh.Activate

    xlWsh.Cells.Rows(4).AutoFilter

    xlWsh.Cells.Rows(4).EntireColumn.AutoFit



    rs.Close

    Set rs = Nothing

    'Remove prompts to save the report

    ApXL.DisplayAlerts = False

    xlWBk.Save    'As "Put the path where you want the file saved OR change to just save your existing file", 51

    ApXL.DisplayAlerts = True

    Exit Function

Err_Handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

    Exit Function

End Function

IM window
strTQname: qryMonthlyCalc
strSheetName: qryMonthlyCalc
strPath: F:\Users\Paul\Documents\Diabetes.xlsx

 

[xavier.batlle]

I don't know if this can can help you: I've tested the code on 32 bit MS Access 365 using Early Binding with no issues:

Call SendToExcel ("Consulta1","Sheet1","c:\Tmp\Cambridge.xlsx")
strTQname: Consulta1
strSheetName: Sheet1
strPath: c:\Tmp\Cambridge.xlsx

 

[Josef P.]

Does the code work with a manually created new empty Excel file?
You may see a message if you make the application visible before opening it.

Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True

=>

ApXL.Visible = True
Set xlWBk = ApXL.Workbooks.Open(strPath)

 

[Gasman]

This is not even working in Excel

Sub OpenFile()
Workbooks.Open ("F:\Users\Paul\Documents\Diabetes.xlsx")
End Sub

yet this in another workbook is?

Sub GillianOutput()
'
' GillianOutput Macro
' Supply values only sheet for Gillian
Dim strPath As String, strFileName As String, strSaveFile As String, strGCDName As String
Dim lngTotalsRow As Long, iLastRow As Integer, iRowsDeleted As Integer
strPath = "F:\Users\Paul\Documents\GCD\"
strFileName = "Gillian.xlsx"
strGCDName = "GCD.xlsm"
    'Update_Display (False)
    Workbooks.Open Filename:=strPath & strGCDName
    Worksheets("SF66OEK").Activate

Something is up with the file, and I just tried the first block of code with the second filename and it opened that workbook.

 

[Gasman]

Something to do with that workbook. The Gillian one opens fine.
Used it on a copy of that workbook and that opens???

I will call this quits now.
Thank you for the replies.

 

[Josef P.]

Is there perhaps another Excel instance running in the background with this workbook?

 

[Gasman]

Is there perhaps another Excel instance running in the background with this workbook?

No, none, I checked that as well.
Something awry with that workbook.

 

[gemma-the-husky]

In your second example you are using a subfolder called GCD. In the first example there's no subfolder. Are you are you have the file/ folder name correct.

Can you not include a file browser to select the file you need?

 

[Gasman]

In your second example you are using a subfolder called GCD. In the first example there's no subfolder. Are you are you have the file/ folder name correct.

Can you not include a file browser to select the file you need?

Yes, I showed a picture of the file properties in the initial post.
I could, but as I want to populate a certain file all the time, that would be a right pain.

Turns out, there was something amiss with the file.
I created a new workbook named the same and that works fine.

 

[The_Doc_Man]

In my experience (which certainly ISN'T all-encompassing), error 1004 in Excel usually means that in some drill-down qualification reference, one of the elements in the reference isn't correctly instantiated. That is, if you have a reference like A.B.C.D.E, one of A, B, C, or D is null/nothing. This can be caused if one of those elements is public (supplied externally to the situation) and somehow has become dereferenced OR over-referenced.

 

[MajP]

Please comment out the error checking so we know where this breaks. Or step through it and show us.
Add debug. prints on the parameters and resolved paths and worksheet names. A bad string could cause this to fail. There are file and folder names that are legal in windows but will fail in vba, so like to see the real names.

 

[Gasman]

It was failing when trying to open the workbook.
I was stepping through each line as I reccommend others to do. I could open the file OK in Excel, so it never occurred to me that could be the issue.
As I have mentioned, something was wrong with the file. Replacing a file with the same name in the same path, it now works.

Nothing in the code was changed.

 

[gemma-the-husky]

I think I've seen that exporting an excel file fails in some cases if an existing excel file has a different format. It. Not sure if that's sometimes or every time..in such a case you could delete the old file first, and then create a new one.

 

[Gasman]

Something was wrong with the file.
I knew I had done it in the past in Excel, so I just tried a one liner in Excel and had the same issue.
I then tried another workbook in Excel in existing code, which opened fine.
I then tried that other workbook from Access and that worked fine.

So I deleted that file, created a new one same name, same extension, same type and it has been working since.

Just never occurred to me that the file would be at fault, as I could open it OK in Excel.

 

[Edgar_]

Can you provide the bad file for analysis?

 

[Gasman]

Can you provide the bad file for analysis?

No, sorry, I deleted it, to make way for the new file.

Edit: Well I only deleted it, not shift deleted it, so renamed my good file and restored the bad one.
That failed again, yet this time when I went into it with Excel, it asked me if I wanted to recover it. I said yes and then saved it again.
Now that one opens from Access.