Can't repeat output to Excel

[apestaart]

I have created a form with a button box to transfer data to an Excel spreadsheet. I used code from "Hellen Feddema" (Access archon colulmn #66)"writing data from an access table to a worksheet created from an template".
this works, but only once!
What I mean is te following:I print the spreadsheet and close it. Then I go to an other record in my form and press the button again, it opens the excel spreadsheet but no data has been transferred. I checked if all new variables where set for the new transfer and that was correct.
What went wrong I your need help.
Apestaart

 

[1DMF]

if the target Excel spreadsheet is the same name, you need to kill it first before doing the export.

i have the same problem when issuing an excel transfer spreadsheet command to a shared xls file, what I do is kill the file then issue the transfer command, that way it always has the new data.

Kill "Path\FileName"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryName", "FileName", True

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[hwkranger]

1DMF has the solution... i've had problems like this in the past. What i've done to fix it is the following:

    With objExcel
        Dim strFileName As String
        strFileName = "c:\temp\" & intBucket & "forcast" & Format(Now, "mmddyyhhmmss") & ".xls"
        .ActiveWorkbook.SaveAs strFileName, , , , , False
        .ActiveWorkbook.Close
        .Quit
    End With
    Build120_180Forecast = strFileName
    Set objExcel = Nothing

You MUST dereference everything for excel to work properly. When you use the transfer it doesn't dereference everything. Check you Running apps after you run it the first time, you'll see that Excel is still running!!! that's why it doesn't work the 2nd time.

I found this after about 6 months of trouble shooting... so this is a huge pain in the butt!

Randall Vollen
National City Bank Corp.

 

[1DMF]

I found even though I have the Excel var set to nothing it still didn't work.

I also find that if you use the export tool within a query and choose an existing excel as the target file it doesn't overwrite it!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[apestaart]

1DMF (Programmer) and Randall,

The suggestion Kill "Path\FileName" kills my template and that is not what I want.
I tried the code of hwkranger and it works smoothly.
Thanks both for your answers.
Regard,
Apestaart

 

[1DMF]

cool glad you got it sorted!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.