DoCmd.TransferSpreadsheet acExport Question

[SunnyG]

Hello All,

This is what I have so far and it works, I tested it.

Could someone please help tweak this a bit so that the file does not get overwritten each time the export happens.

Is there a way to have the code create a new file when run instead of having the export go into an existing file and have it automatically named with the date it is run. The name could be DTS Export with the date it runs.

I need to run this each week and I need to have the file renamed each week with the date of when it runs o that it does not get overwritten. Any help would be greatly appreciated.

Function exportDtsData()

DoCmd.TransferSpreadsheet acExport, , "Dashboard 1 - Exception Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Dashboard 1 - Exception Status"
DoCmd.TransferSpreadsheet acExport, , "Dashboard 2 - LOB Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Dashboard 2 - LOB Status"
DoCmd.TransferSpreadsheet acExport, , "Dashboard 3 - Region Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Dashboard 3 - Region Status"
DoCmd.TransferSpreadsheet acExport, , "Data with Officer Name 85 and 86s only", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Data with Officer Name 85 and 86s only"
DoCmd.TransferSpreadsheet acExport, , "Status - Do Not Call (88)", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Status - Do Not Call (88)"
DoCmd.TransferSpreadsheet acExport, , "Document - LD0102", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Document - LD0102"
DoCmd.TransferSpreadsheet acExport, , "Perfected Documents", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Perfected Documents"
DoCmd.TransferSpreadsheet acExport, , "Data with Officer Name 87s only", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Data with Officer Name 87s only"
DoCmd.TransferSpreadsheet acExport, , "Data with Officer Name no 87", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHARED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master.xls", False, "Data with Officer Name no 87"
End Function

 

[pbaldy]

You can add the date like:

"ExistingPathWithoutExtension" & Format(Date(), "yyyymmdd" & ".xls"

 

[SunnyG]

Thank you for your help. Could you please tell me where this piece of code goes? I'm sorry but I am very new at this.

 

[pbaldy]

In the file name argument where you have the very long path starting with \\.

 

[SunnyG]

again very sorry but I don't know where to put the code weak you suggested. When you refer to the file name argument I don't know where that is. I know enough to be dangerous, not much at all. Thank you for your help.

 

[pbaldy]

You don't see your file name in there? Try

DoCmd.TransferSpreadsheet acExport, , "Dashboard 1 - Exception Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyyymmdd") & ".xls", False, "Dashboard 1 - Exception Status"

 

[SunnyG]

Thank you for your help and time.Not surewhat I didwrong, I got a syntax error.

Function exportDtsData()

DoCmd.TransferSpreadsheet acExport, , "Dashboard 1 - Exception Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Dashboard 1 - Exception Status"
DoCmd.TransferSpreadsheet acExport, , "Dashboard 2 - LOB Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Dashboard 2 - LOB Status"
DoCmd.TransferSpreadsheet acExport, , "Dashboard 3 - Region Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Dashboard 3 - Region Status"
DoCmd.TransferSpreadsheet acExport, , "Data with Officer Name 85 and 86s only", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Data with Officer Name 85 and 86s only"
DoCmd.TransferSpreadsheet acExport, , "Status - Do Not Call (88)", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Status - Do Not Call (88)"
DoCmd.TransferSpreadsheet acExport, , "Document - LD0102", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Document - LD0102"
DoCmd.TransferSpreadsheet acExport, , "Perfected Documents", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Perfected Documents"
DoCmd.TransferSpreadsheet acExport, , "Data with Officer Name 87s only", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Data with Officer Name 87s only"
DoCmd.TransferSpreadsheet acExport, , "Data with Officer Name no 87", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyymmdd" & ".xls", False, "Data with Officer Name no 87"

End Function

 

[pbaldy]

You were too fast for me. When I first posted, I forgot the closing parentheses on the Format() function. I thought I edited it fast enough. Try this

DoCmd.TransferSpreadsheet acExport, , "Dashboard 1 - Exception Status", "\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_ SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master" & Format(Date(), "yyyymmdd") & ".xls", False, "Dashboard 1 - Exception Status"

 

[SunnyG]

Thank you again. I am getting a run time error 3044: it says it is not a valid file path.
When I added your () to the date, the code editor accepted it but then it disappeared. Any thoughts? Thank you again very much.

 

[pbaldy]

Sometimes the code editor does that, so I wouldn't worry about that. Is the rest of the path valid? It should result in a path/file of

\\corp\dfs\Retail_Shared\GA016\F3A_02\RETAIL_ SHAR ED\AQCP\WBCS Admin\WBCS Analytics\REPORTING\Weekly\DTS\DTS Dashboard Master20150916.xls

Which frankly is long but should still work presuming the folders exist.

 

[SunnyG]

Thank you very much! This worked great. Is there also a way to make the same code export to 2 different locations? Is it possible to also add this location somehow? Thank you very much again for all of your help today it is greatly appreciated.

 

[pbaldy]

Happy to help and welcome to the site by the way!

You want the same file in 2 locations? Two options, repeat the TransferSpreadsheet for the second location, or use FileCopy after creating the file. There's no option to have the TransferSpreadsheet create 2 files that I know of. Am I reading right that all of the transfers use the same file name and path, so presumably are creating separate sheets in a single file? If so, you could do one FileCopy at the end.

 

[SunnyG]

Thank you again. Yes all the files are being exported into 1 excel file into the same location. But I also need to send this file to go here:
\\ga016a204\wwwsites\WLS-develop.suntrust.com\REPORTS\
could you please tell me how to use the FileCopy function?

</SPAN>

 

[pbaldy]

There should be a good description and example in VBA help.

 

[SunnyG]

Thank you very much Paul, I really appreciate all of your time and help today. What you provided works perfectly!

Take care and be well!

 

[SunnyG]

This thread is solved.

 

[pbaldy]

Thank you very much Paul, I really appreciate all of your time and help today. What you provided works perfectly!

Take care and be well!

Happy to help and you too!