Transferspreadsheet Overwrite

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Musa - 08 May 2008 18:04 GMT

Hello,

Each time a User clicks on the Command to Transfer the data to Excel, I would
like the new export to do ONE of the following 1) Create a New Version of the
spreadsheet in the same drive as the exisiting or 2) replace the exisitng
Excel workbook with an updated version.

I'm using Transferspreadsheet to Export the data.
Thanks

Reply to this Message

Ron2006 - 08 May 2008 21:12 GMT

To do the first, make the name of the spreadsheet unique so that it
will always create a new spreadsheet.

To do the first DON'T do the above. Have the spreadsheet name the
same. or test if the spreadsheet is there and if it is then delete it
and THEN do the transferspreadsheet.

Ron

Reply to this Message

Musa - 08 May 2008 22:18 GMT

How would I test for the following ?

Private Sub Command73_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Initialdata",
"C:\GERIATRIC DATA.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Assessmentdata",
"C:\GERIATRIC DATA.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Followupdata",
"C:\GERIATRIC DATA.xls"
MsgBox ("You have sucessfully exported GERIATRIC DATA to your C: Drive")
End Sub

>To do the first, make the name of the spreadsheet unique so that it
>will always create a new spreadsheet.
[quoted text clipped - 4 lines]
>
>Ron

Reply to this Message

Ron2006 - 09 May 2008 13:00 GMT

If you want to delete the file

if len(dir("C:\GERIATRIC DATA.xls"))>0
then kill "C:\GERIATRIC DATA.xls"
endif

Beyond that I am not sure how to answer your question. You said you
want it to do one of two things but never indicated the criteria for
making the decision of which to do.

To always have a unique name try changing the name to:

"C:\GERIATRIC DATA " & year(date()) & format(month(date()),"00")
& format(day(date()),"00") & ".xls"

This will put a date stamp on the file name. if it can be done more
than once during the day AND you want a different file for each, then
add hours and minutes to that stamp.

You will want to use the format or otherwise you will not be able to
tell the difference between a file created for January 21st and one
for December 1st. And by putting the year first it will always be in
sequence by date simply by sorting by name.

Ron

Reply to this Message

Ron2006 - 09 May 2008 13:19 GMT

Here is something else to think about.

You are using: DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "Initialdata",

IF you have any memo fields in you exported data and you use the
"acSpreadsheetTypeExcel9" then those fields will get truncated to 256.

You need to use acSpreadsheetTypeExcel8 instead in order not to get
the truncation problem.

Ron

Reply to this Message

Musa - 09 May 2008 19:43 GMT

Thanks Ron.

>Here is something else to think about.
>
[quoted text clipped - 8 lines]
>
>Ron

Reply to this Message