Transfer Spreadsheet Won't Work if Excel is Already Open

HorsepowerX

Registered User.
Local time
Today, 05:45
Joined
Sep 17, 2013
Messages
10
I am extremely new to writing code, but was able to successfully write code that transfers the data in my form to and excel spreadsheet template and saves it with a different name. The code works perfect and without issue as long as Excel is not running. However if Excel is already open the template remains blank and a warning message appears saying that the form is locked for editing. Below is the code I used. Its probably not the most efficient way of doing it, but like I said, I am a beginner. I appreciate any help or feedback

Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlwrkBk = xlApp.Workbooks.Open("G:\HC5D\Group Files\PCF\PCF Form.xlsx")

Set xlSheet = xlwrkBk.Worksheets("Export")

xlSheet.Rows("1:100").ClearContents

DoCmd.OpenQuery "qselPCF_Form", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acExport, 10, "qselPCF_Form","G:\HC5D\Group Files\PCF\PCF Form", False, "Eric"
DoCmd.SetWarnings True
DoCmd.Close acQuery, "qselPCF_Form"
Beep
MsgBox "A new PCF has been created and saved in G:HC5\\PCF Files", vbInformation, "Status"


Set xlSheet = Nothing
xlwrkBk.SaveAs ("G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.txtTitle & " " & Me.Group & " " & Me.FY & ".xlsx")

Set xlwrkBk = Nothing


xlApp.Visible = True

Set xlApp = Nothing

End Sub


I apologize if I posted this wrong. This is my first attempt. I usually find answers in other posts, but didn't have any luck with this one. Thank you for looking.
 
You could try checking to see if Excel is running and instead of the:
Set xlApp = New Excel.Application
try

Code:
Set xlApp = GetObject(, "Excel.Application")
 
If xlApp Is Nothing Then 'start Excel using CreateObject
     Set xlApp = CreateObject("Excel.Application")
End If

and then just open your file.
this should prevent having multiple instances of Excel running.

David
 
It worked like a champ!!!! Thank you so much for taking the time out of your day to help me. I appreciate it!!

Thank you,
 

Users who are viewing this thread

Back
Top Bottom