I have exported to Excel but the file is copied twice?

[ThriftyFinanceGirl]

Let me explain... I have done a "file copy" command to copy a template, then
export into that template.

The file copy command works perfectly, and the new file is saved where it
should be, however, when the Excel application opens, it is opening ANOTHER
copy of the file (and giving it the name "filename"1). Then when the code is
complete I get the save as dialog box and it isn't even pointing to the place
where I made the original copy. Below is my code...(partial of course) Does
anyone see why this is happening?
--------------------------------------------
'name and full path to use to save the xls file
strWorkBook = "\\Nas01\dol_exch\Departments\Accounting\Sales " & _
"Tax\Sales Tax Database\MasterRecons\MasterJE_" & intmonth &
intyear & ".xls"

strTemplate = "\\Nas01\dol_exch\Departments\Accounting\Sales " & _
"Tax\Sales Tax Database\Templates\MasterReconJE.xlt"

'this command copies the file and gives it the new name
FileCopy strTemplate, strWorkBook
blnEXCEL = False

' True = first row will be header row
' (the names of the fields from the recordset)
'False = No header row
blnHeaderRow = False

' Create an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' True = visible while processing
' False = hidden while processing
xlx.Visible = True

' Set the EXCEL file to write the data into
Set xlw = xlx.Workbooks.Open(strWorkBook)
Set xls = xlw.Worksheets("VarianceJE")
.........................................................................

 

[Jack Leach]

when the Excel application opens, it is opening ANOTHER

copy of the file (and giving it the name "filename"1).

Isn't this standard procedure for opening of a template file? When you
double click a template in explorer or the desktop or whatever, you aren't
actually opening the *template* - it instead creates a new, unsaved file
*from* the template.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[ThriftyFinanceGirl]

Well, yes, I think it is... however, my thought process was that I wanted to
create the name of the new file programmatically (basically with the
month/year) so that they didn't have too. So are you thinking that I should
just have Excel open the template and not make a copy? How then would I
programmatically add the month and year that I need when it is time to save
the file?

 

[Jack Leach]

Unfortunately I have no experience whatsoever with Excel Auotmation, so I'm
not sure quite how you would accomplish this. If I had to take a shot in the
dark, I would say open the file from the template, transfer your data, and
use automation to SaveAs with the desired filename.

But I'm not sure if you're using Automation or TransferSpreadsheet. I don't
see how you may be able to do this if you are using TransferSpreadsheet, so
either way you need someone more versed than I to advise.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Ken Snell MVP]

Per Jack's questions, can you post all the VBA code that you're using?

 

[ThriftyFinanceGirl]

Sure... Thanks for any help in advance!

Public Sub WriteJEMaster()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
Dim intmonth, intyear As Integer

'turn on the hourglass
DoCmd.Hourglass True

'set the month and year
intmonth = Forms!frmMain!txtMonth
intyear = Forms!frmMain!txtYear

'name and full path to use to save the xls file
strWorkBook = "\\Nas01\dol_exch\Departments\Accounting\Sales " & _
"Tax\Sales Tax Database\MasterRecons\MasterJE_" & intmonth &
intyear & ".xls"

strTemplate = "\\Nas01\dol_exch\Departments\Accounting\Sales " & _
"Tax\Sales Tax Database\Templates\MasterReconJE.xlt"

'this command copies the file and gives it the new name
FileCopy strTemplate, strWorkBook
blnEXCEL = False

' True = first row will be header row
' (the names of the fields from the recordset)
'False = No header row
blnHeaderRow = False

' Create an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' True = visible while processing
' False = hidden while processing
xlx.Visible = True

' Set the EXCEL file to write the data into
Set xlw = xlx.Workbooks.Open(strWorkBook)

' DO NOT change the name of the worksheet
'unless it is also changed here
Set xls = xlw.Worksheets("VarianceJE")

' Cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A17")

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("JEMasterTemp", dbOpenDynaset, dbReadOnly)

If rst.EOF = False And rst.BOF = False Then

rst.MoveFirst

If blnHeaderRow = True Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value =
rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1, 0)
End If

' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value =
rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1, 0)
Loop

End If

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

' Close the EXCEL file while saving the file, and clean up the EXCEL
objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing
DoCmd.Hourglass False
MsgBox "Your Journal Entry has been created! Your file is saved at " &
strWorkBook

'ask the user if they want the JE opened
If MsgBox("Do you want to open the file now?", vbYesNo, "S.T.A.N. Info")
= vbYes Then
Excel.Application.Workbooks.Open strWorkBook
Excel.Application.Visible = True
End If



End Sub

 

[ThriftyFinanceGirl]

Thanks ever so much guys! The problem seems to be using a Template itself
(instead of just copying a regular .xls file) Thanks!

 

[Ken Snell MVP]

Sorry that I didn't respond sooner; somehow, I overlooked that you'd posted
a reply with the code.