Exporting data to excel

Mansoor Ahmad

Registered User.
Local time
Today, 23:43
Joined
Jan 20, 2003
Messages
140
Hello All

In my database I want certain data to be exported to excel spreadsheet. That spreadsheet is then further linked to another excel file. So basically each time when the query is run new set of data updates in to the linked sheet.

I want to use macro for this purpose. I have tried 'OutputTo' action in macros. It does export the data into specified excel file but when I run the macro 2nd time, it prompts that the excel sheet mentioned in output file argument already exist, do you want to replace it. If I click on yes, obviously it delects the file and if I have saved the linked sheet in the same excel spreadsheet, that would delete as well.

Is there a way that this macro only updates new data without deleting the destination file?
 
This is what I use for a similar purpose. As an on_click event of a command button:

Code:
DoCmd.SetWarnings False
'deletes the excel file if it exists
    If Dir("pathtoyourexcelfile") <> "" Then
    Kill "pathtoyourexcelfile"
    End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "nameoftable", "pathtoyourexcelfile"
DoCmd.SetWarnings True

Now the kill statement deletes the excel file before it creates a new one. Therefore I suggest you have the linked data in a different workbook. I think that should work, although I haven´t actually tried it.

You could probably delete just a particular spreadsheet of a workbook and replace it with new data, but I would guess this is easier and faster.

Fuga.
 
Last edited:
Thank you very much for your reply. I will try it and come back to you soon.

Thanks
 
Thank you very much again for your reply. I have tried and it works fine.

Now there is another related question.

As this data is being exported to one excel file that is linked to another file. The linked file is the one I want to open when I click on command button. As I am very new to these codes, I cannot find any suitable command that would open linked excel file after the data has been exported. Can you please suggest one that I can put in the end of above code.

Thank you.
 
try this,add this code after the last docmd
Dim XLAddress as string
XLAddress = the full path & filename of your excel file
FollowHyperlink XLAddress
 

Users who are viewing this thread

Back
Top Bottom