Create Excel File from Access Help

[HorsepowerX]

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

 

[namliam]

what is your mcrPCF??

One option may be to open your template file as a template, i.e. Read only
There is a flag in the workbooks.open that you can set to open the file as read only, that should prevent atleast one of your issues.
i.e.
Set xlwrkBk = xlApp.Workbooks.Open("G:\HC5D\Group Files\PCF\PCF Form.xlsx",,True)

 

[HorsepowerX]

Here is the code for the mcrPCF:

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


mcrPCF_Exit:
Exit Function

mcrPCF_Err:
MsgBox Error$
Resume mcrPCF_Exit

 

[HorsepowerX]

I appreciate you taking the time to help me with this. I tried your suggestion and it did take care of the "read only" problem. However, the problem I encountered was that the data was saved to the "PCF Form". The newly created PCF file was blank. So its writing to the Excel Form, but when it saves the file with the new name there is no data. Not sure if that helps you at all.

 

[namliam]

Does your excel sheet have a named range "Eric" ?? If so is it big enough to recieve the data that you are sending to it?

You are sending the data to the file once it is already open, i.e. you are opening the file the doing transferspreadsheet. This is the reason the data isnt there.

In your "error messages" your excel sheet looks like it has al kinds of headers and formatting. where as in your code you are deleting rows 1 thru 100

You might have a look at this thread of mine:
http://www.access-programmers.co.uk/forums/showthread.php?t=259994
Where I posted some code that may be usefull to you.

 

[HorsepowerX]

Thank you for your reply. I cheated on the excel sheet. I just transfer the data to a hidden worksheet, which populates the worksheet in my screenshot. "Eric" is the name range for the export.

The frustrating thing is that with Code 1 the transferspreadsheet works fine as long as Excel is already open (see below). When I changed the code to code 2 it works fine, but only if Excel is not open. I should mention that code 2 worked both ways (Excel open or closed) for a week or two. After that I began having issues when Excel was open.

I took a look at your link. I should probably add that I am pretty much a "beginner" when it comes to vba. If you think there is no way for me to get my code I will attempt to do something similar to what you did. Would doing the transferspreadsheet before opening the file then "Save As" new file name, then clear the contents on the original form and save work? I apologize if this doesn't make sense.

Code 1: (Only works if Excel is open)
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: (Works if Excel is not open)
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

 

[namliam]

Thing is you have the conflict as far as I can see, having the file open AND doing a transferspreadsheet to it at the same time.

What I would do is this:
1) Make sure your template file is empty and waiting your data
2) Not open the file in excel for now
3) Use FileCopy to copy the EMPTY template to a new file
4) Use Transferspreadsheet in the now existing new file

Something like:

Dim TargetName as string
TargetName = "G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.Group & " " & Me.FY & ".xlsx"

FileCopy "G:\HC5D\Group Files\PCF\PCF Form.xlsx", Targetname
DoCmd.TransferSpreadsheet acExport, 10, "qselPCF_Form", Targetname, False, "Eric"

You can then open the file if you are married to the concept of opening the file. For this purpose you can also use FollowHyperlink
FollowHyperlink Targetname

 

[HorsepowerX]

Your idea worked awesome! I couldn't believe how much faster and more efficient it works! Thank you!!!

 

[namliam]

Simple solutions usually are the most fun

 

[HorsepowerX]

Ok so I have a new problem. Your code worked great the first time, but now anytime I try to generate the Excel file the data won't transfer. The file name changes, but the data does not change. It is still the data from the first file I created. Any ideas what is causing this or what's missing? The code is below. I appreciate the help.

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

Dim TargetName As String
TargetName = "G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.Group & " " & Me.FY & ".xlsx"

FileCopy "G:\HC5D\Group Files\PCF\PCF Form.xlsx", TargetName
DoCmd.TransferSpreadsheet acExport, 10, "PCF_FormExport", TargetName, False, "Eric"

Set xlSheet = Nothing

Set xlwrkBk = Nothing

Set xlApp = Nothing


FollowHyperlink TargetName

End Sub

 

[namliam]

First of all you dont need all the excel crap in that procedure....

Private Sub ExcelClear_Click()

Dim TargetName As String
TargetName = "G:\HC5\PCF\PCF-0" & Me.PCF_ & " " & Me.txtSupplier & " " & Me.Group & " " & Me.FY & ".xlsx"

FileCopy "G:\HC5D\Group Files\PCF\PCF Form.xlsx", TargetName
DoCmd.TransferSpreadsheet acExport, 10, "PCF_FormExport", TargetName, False, "Eric"

FollowHyperlink TargetName

End Sub

assuming your standard file PCF Form.xlsx is empty to start with... The only thing I can think of that your exported data stays the same, is that there is an error in your query PCF_FormExport that you are exporting.

Perhaps the fact that you are using "10" i.e. acSpreadsheetTypeExcel12Xml, perhaps is a problem? Why use 10 over the ac variable in the first place?

 

[HorsepowerX]

namliam,
I was just going through my old email and saw this thread. I decided to click on it for some reason and realized I never responded. I can't believe I did that. Anyways, Your suggestion works flawless! Thank you so much for helping me! I really appreciate it and hope you are still active on this forum to see this.

thank you!