Exporting query to shared drive

[matey56]

Hi all,


I'm using VBA to export a query to Excel. The path is a OneDrive location that is synced to all of our C: drives. It works for me, but when other users try to export it gives them an error because the path has my username (Mateyr).

My question is - what syntax can I use to replace my username so it works for everyone?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_Product_Name", "C:\Users\Mateyr\ABC\Documents\Database\rpt_Product_Name.xlsx", True

[Welshgasman]

Their username?

Look at the Environ() function.

[matey56]

I used the " & Environ("username") & " function and got it to work.

[matey56]

So the query exports fine, but now I have a different issue. It exports the query to the designated folder but each time we export it, instead of replacing the existing .xlsx file it just adds a new tab. How do I change the code to replace the existing file?

Here's what I have right now.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_Product_Name", "C:\Users\" & Environ("username") & "\ABC\Documents\Database\rpt_Product_Name.xlsx", True

[Welshgasman]

Delete the file first? as it does not do that for me?, however I only exported to a temp local drive.
Try that location and see if it still does it.

[matey56]

Delete the file first? as it does not do that for me?, however I only exported to a temp local drive.
Try that location and see if it still does it.

Yeah, I can ask the users to delete the file first, but I thought if it asked them to replace it that would be easier.

[Welshgasman]

Yeah, I can ask the users to delete the file first, but I thought if it asked them to replace it that would be easier.

No!, you delete the file with VBA. Test to see if it exists first, if it does, delete it.
Then Transfer the query.
I would test on a non OneDrive local folder first, as it worked for me. Maybe something to do with OneDrive, but I cannot think what that might be.

[matey56]

No!, you delete the file with VBA. Test to see if it exists first, if it does, delete it.
Then Transfer the query.
I would test on a non OneDrive local folder first, as it worked for me. Maybe something to do with OneDrive, but I cannot think what that might be.

Ah, I see. What is the syntax to delete the file first with VBA? Interestingly enough I switched the code to save it to my desktop and I get the same thing. It doesn't replace the file - just adds a new tab.

[Welshgasman]

Have a google to see how to delete a file with vba. You probably need to do the same to see how you test if a file exists.

[Welshgasman]

Have a google to see how to delete a file with vba. You probably need to do the same to see how you test if a file exists.

[matey56]

Have a google to see how to delete a file with vba. You probably need to do the same to see how you test if a file exists.

I got it working. I used the Kill function. It's just Kill "path/file"

[Welshgasman]

And to see if it exists?