Office 365 problems opening Excel with DoCmd.TransferSpreadsheet (1 Viewer)

Danick

Registered User.
Local time
Today, 18:07
Joined
Sep 23, 2008
Messages
351
My company recently moved to Office 365 and now I'm having issues using DoCmd.TransferSpreadsheet
This VBA used to transfer a query to a Worksheet and then open the Workbook

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, WbSheet, CurrentProject.Path & "\" & WbName, True

Now, the Workbook opens and then closes again. The transferred data if there, but I have to open the workbook using File Explorer or Excel.

Anyone know what could be giving this problem?
 

Isaac

Lifelong Learner
Local time
Today, 15:07
Joined
Mar 14, 2017
Messages
8,738
1. Can you post the entire chunk of code for this procedure?
2. Is the destination workbook an xlsb or xlsm with any VBA code of its own inside?
 

Danick

Registered User.
Local time
Today, 18:07
Joined
Sep 23, 2008
Messages
351
Here you go. This was working fine with Office 2010, 2013 and 2016. Can't figure out why Excel is opening and closing. Unless there was something else (like security software) that was also installed...

Code:
Private Sub cmdExport_Click()

    Dim Excel As Excel.Application
    Dim ExcelOpened As Boolean
    
    ExcelOpened = False
    On Error Resume Next
    Set Excel = GetObject(, "Excel.Application")
    If Excel Is Nothing Then
        Set Excel = New Excel.Application
        ExcelOpened = True
    End If
    On Error GoTo 0

    With Excel
        If ExcelOpened Then
            .Visible = True
            .Workbooks.Add
        End If
        .ActiveWorkbook.Close False
        .Quit
    End With

    
    Dim WbName As String
    Dim WbSheet As String
    
    WbSheet = "qryName"
    WbName = "WorkBookName.xls"
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, WbSheet, CurrentProject.Path & "\" & WbName, True
 
    Excel.Workbooks.Open Application.CurrentProject.Path & "\" & WbName, True, False
    Excel.Visible = True
    Excel.UserControl = True
 

End Sub
 

Isaac

Lifelong Learner
Local time
Today, 15:07
Joined
Mar 14, 2017
Messages
8,738
This is confusing. The middle part is adding a workbook, then closing it without saving - effectively creating a workbook, then erasing it from existence. Is this necessary?

What is your end goal? To export your results to an existing workbook, or to always create a new one, or something else?
 

Danick

Registered User.
Local time
Today, 18:07
Joined
Sep 23, 2008
Messages
351
Thanks for catching that. I've been working with various versions of this same code for so long (since 2003 days) that I didn't think to re-examine it again. This was used for workbooks where I needed to always create new temporary workbook and then have a formatted workbook update links to that newly created workbook.

In any case, it's not needed for this purpose. So after removing that middle part of the code, Excel opens fine and stays opened.
Thanks for your help
 

Minty

AWF VIP
Local time
Today, 22:07
Joined
Jul 26, 2013
Messages
10,355
This is a great example of what we mean when we say VBA has become "fussier" over the years.

There are things that have worked for aeons that suddenly pack up with no changes being made, and sometimes we (the allegedly vaguely knowledgeable people on here) ask can we see the code, as although there was nothing intrinsically wrong, there might be now.
 

Isaac

Lifelong Learner
Local time
Today, 15:07
Joined
Mar 14, 2017
Messages
8,738
Thanks for catching that. I've been working with various versions of this same code for so long (since 2003 days) that I didn't think to re-examine it again. This was used for workbooks where I needed to always create new temporary workbook and then have a formatted workbook update links to that newly created workbook.

In any case, it's not needed for this purpose. So after removing that middle part of the code, Excel opens fine and stays opened.
Thanks for your help
You're welcome! Glad you got it working.
 

Users who are viewing this thread

Top Bottom