Issue with TransferSpreadsheet into existing Excel file.

[RZ15]

Hi,

I am trying to use the transferspreadsheet command to transfer a table into an excel file i have. I'm using the following code:

Private Sub cmdOK_Click()
    'Run Make-Table query without warnings so I can export the updated table later
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryPriLis", acViewNormal
    DoCmd.Close acTable, "Perpetual"
    DoCmd.SetWarnings True
 
    Dim path As String
    Dim Xl As Object
    Dim xlsheet As Object
    path = "C:\See Through June 2007.xls"
 
    'Open Excel file and make it visible and turn off warnings
    Set Xl = GetObject(path)
    Xl.Application.Visible = True
    Xl.Parent.Windows(1).Visible = True
    Set xlsheet = Xl.Worksheets(22)
    Xl.Application.DisplayAlerts = False
 
    'Delete the worksheet and then transfer in the new data
    xlsheet.Delete
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Perpetual", path, , "Perpetual"
 
    'Save, turn warnings back on, quit and release variables
    Xl.Application.Save
    Xl.Application.DisplayAlerts = True
    Xl.Application.Quit
    Set Xl = Nothing
    Set xlsheet = Nothing
End Sub
 

The issue is with the transferspreadsheet part. I get a "Runtime Error 3422: Cannot modify table structure. Another User has the table open." Everything before and after it works fine.

If anyone has any insight, it's well appreciated.

Raza

[Stang02GT]

Is this DB shared over a network?

Becuse it sounds like from the error message that someone has your DB open on another machine, which wouldn't allow you to do anything to it. Check and make sure that when you attept to run this code that you are the only person in the DB.

[RZ15]

I am running it off my harddrive. The data is on a server, but I made it a make-table query for that purpose.

I don't think that is the problem.