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.
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.