By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,562 Members | 1,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,562 IT Pros & Developers. It's quick & easy.

Issue with TransferSpreadsheet into existing Excel file.

P: 53

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2.     'Run Make-Table query without warnings so I can export the updated table later
  3.     DoCmd.SetWarnings False
  4.     DoCmd.OpenQuery "qryPriLis", acViewNormal
  5.     DoCmd.Close acTable, "Perpetual"
  6.     DoCmd.SetWarnings True
  8.     Dim path As String
  9.     Dim Xl As Object
  10.     Dim xlsheet As Object
  11.     path = "C:\See Through June 2007.xls"
  13.     'Open Excel file and make it visible and turn off warnings
  14.     Set Xl = GetObject(path)
  15.     Xl.Application.Visible = True
  16.     Xl.Parent.Windows(1).Visible = True
  17.     Set xlsheet = Xl.Worksheets(22)
  18.     Xl.Application.DisplayAlerts = False
  20.     'Delete the worksheet and then transfer in the new data
  21.     xlsheet.Delete
  22.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Perpetual", path, , "Perpetual"
  24.     'Save, turn warnings back on, quit and release variables
  25.     Xl.Application.Save
  26.     Xl.Application.DisplayAlerts = True
  27.     Xl.Application.Quit
  28.     Set Xl = Nothing
  29.     Set xlsheet = Nothing
  30. 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.

Sep 21 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,208
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.
Sep 21 '07 #2

P: 53
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.
Sep 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.