HorsepowerX
Registered User.
- Local time
- Today, 00:47
- Joined
- Sep 17, 2013
- Messages
- 10
I have a database that my group at work uses. One of the features is after they enter data in Access they can press a button that will generate an Excel file to send to suppliers. The code worked fine for a while, but I began having issues. I have had two issues the first one was it wouldn't transfer the data if Excel was not open (Code 1). It worked fine if Excel was open, but if there was no instance of Excel I would get an error saying "cannot get object".
I came up with a fix (Code 2) that worked if Excel was open or closed. It worked for a little while. However, now when I run it and Excel is open it will only write to the Template and say it's locked for editing. It will create the Excel file, but not paste the data to the new file. I put both codes below. Code 2 is the current one I use. I believe it is more correct code wise.
CODE 1:
Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then 'start Excel using CreateObject
Set xlApp = CreateObject("Excel.Application")
End If
Set xlwrkBk = xlApp.Workbooks.Open("G:\HC5D\Group Files\PCF\PCF Form.xlsx")
Set xlSheet = xlwrkBk.Worksheets("Export")
xlSheet.Rows("1:100").ClearContents
DoCmd.RunMacro "mcrPCF", 1
Set xlSheet = Nothing
xlwrkBk.SaveAs ("G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.Group & " " & Me.FY & ".xlsx")
Set xlwrkBk = Nothing
xlApp.Visible = True
Set xlApp = Nothing
End Sub
_________________________________________________________________
CODE 2 (Current code):
Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If xlApp Is Nothing Then 'start Excel using CreateObject
Set xlApp = CreateObject("Excel.Application")
Else
Set xlApp = GetObject(, "Excel.Application")
End If
Set xlwrkBk = xlApp.Workbooks.Open("G:\HC5D\Group Files\PCF\PCF Form.xlsx")
Set xlSheet = xlwrkBk.Worksheets("Export")
xlSheet.Rows("1:100").ClearContents
DoCmd.RunMacro "mcrPCF", 1
Set xlSheet = Nothing
xlwrkBk.SaveAs ("G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.Group & " " & Me.FY & ".xlsx")
Set xlwrkBk = Nothing
xlApp.Visible = True
Set xlApp = Nothing
End Sub
I attached some screenshots. I appreciate any feedback. Thank you for reading
I came up with a fix (Code 2) that worked if Excel was open or closed. It worked for a little while. However, now when I run it and Excel is open it will only write to the Template and say it's locked for editing. It will create the Excel file, but not paste the data to the new file. I put both codes below. Code 2 is the current one I use. I believe it is more correct code wise.
CODE 1:
Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then 'start Excel using CreateObject
Set xlApp = CreateObject("Excel.Application")
End If
Set xlwrkBk = xlApp.Workbooks.Open("G:\HC5D\Group Files\PCF\PCF Form.xlsx")
Set xlSheet = xlwrkBk.Worksheets("Export")
xlSheet.Rows("1:100").ClearContents
DoCmd.RunMacro "mcrPCF", 1
Set xlSheet = Nothing
xlwrkBk.SaveAs ("G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.Group & " " & Me.FY & ".xlsx")
Set xlwrkBk = Nothing
xlApp.Visible = True
Set xlApp = Nothing
End Sub
_________________________________________________________________
CODE 2 (Current code):
Private Sub ExcelClear_Click()
Dim xlApp As Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
If xlApp Is Nothing Then 'start Excel using CreateObject
Set xlApp = CreateObject("Excel.Application")
Else
Set xlApp = GetObject(, "Excel.Application")
End If
Set xlwrkBk = xlApp.Workbooks.Open("G:\HC5D\Group Files\PCF\PCF Form.xlsx")
Set xlSheet = xlwrkBk.Worksheets("Export")
xlSheet.Rows("1:100").ClearContents
DoCmd.RunMacro "mcrPCF", 1
Set xlSheet = Nothing
xlwrkBk.SaveAs ("G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.Group & " " & Me.FY & ".xlsx")
Set xlwrkBk = Nothing
xlApp.Visible = True
Set xlApp = Nothing
End Sub
I attached some screenshots. I appreciate any feedback. Thank you for reading