Date to String

Topic

New Reply

Does anyone know how I can change the following:

Dim strDate As Date
Dim strFileName As String

strDate = Date – WeekDay(Date – 6) – 7
strFileName = “PH_Exp$” & strDate

DoCmd.CopyObject “”, (strFileName), acTable, “PH_Exp$”
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFileName, c:burncdstrFilename.xls”, True

When the table changes the name to strFileName I get this…. PH_Exp&4/27/01…..I want this….PH_Exp$42701

I have tried changing Dim strDate As Date to strDate As String…but no luck….

Hope someone knows the answer…

Thanks,

Reply | Quote

Replies

Try this:

strFileName = “PH_Exp$” & Format(strDate, “mddyy”)

Instead of:

strFileName = “PH_Exp$” & strDate

Reply | Quote

Paul,

Thanks for the suggestion, it works perfectly.

I thought the fact that the date 4/27/01 had the forward slashes was giving me an error but what you suggested worked and I still get the error, so I guess it wasn’t the forward slashes after all…….

I do appreciate your help.

Thanks

Reply | Quote

Glad to help, Roberta! And I must say it’s nice to connect a face with the name after seeing just your name on the other forum (ElementK).

What’s the error…maybe I can help with that too.

Reply | Quote

Hi Paul,

I’m not sure if I will explain this correctly, but I will give it a try.

I have some tables that I want to either use the Export or TransferSpreadsheet method with. This data needs to be saved in Excel for archiving purposes. Because it is being archived, adding the date to the end of the name of the table is important.

At this time, that isn’t a problem, however, if I use the Export method, (see below) the user must to know the path in which to save it to.

DoCmd.OutputTo acTable, (strFilename), acFormatXLS, , False

If I use the TransferSpreadsheet method, I continue to get the a runtime error which I have attached. (see below)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFilename, “c:HistoryArchivesstrFilename.xls”, True

The thing is is I don’t want the user to have to do anything. I want the table to go straight to the path which is designated in the method.

I am not sure why I can’t add the date (in string format) to the table name and transfer the spreadsheet to the predesignated path.

If you have any suggestions, I would certainly appreciate it.

Thanks alot Paul.

Reply | Quote

I believe the problem lies in your statement:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFilename, “c:HistoryArchivesstrFilename.xls”, True

The argument strFileName should be the name of the table you are exporting as it exists in the database without any date concatenation. The next argument is where you want the date to concatenate with the table name. Something like this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, “tblNameOfTableToExport”, “c:HistoryArchivestblNameOfTableToExport” & format(Date(),”mddyy”) & “.xls”, True

You can do the concatenation on separate lines but don’t confuse the name of the table to export with the destination file name. They should be two separate objects or variables.

BTW, I tried exporting a table using the same method and intentionally supplying an invalid table name and got a similar error message so that’s why I’m focusing on the transferspreadshet method.

Reply | Quote

Paul,

OK…I think I am almost there. I did the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, “PH_Exp$”, “c:HistoryArchivesPH_Exp$” & Format(Date, “mddyy”) & “.xls”, True

and got the attached error.

Thanks again for your help.

Reply | Quote

I think it is the “$” in the table name that is throwing things off. I tried the statement with the $ and without it (had to rename the table) and it worked without but I got a similar error that you did with it. So, try renaming the table and editing the code and try it that way and see if it works.

Reply | Quote

Good Morning Paul….

Excellent!!! Thank you so much for your help…I wish I could do more to repay you but I can’t….You have been a life saver….

Have a nice day and again…THANKS!!!!!!!!!!!

Reply | Quote

Great!! that we figured it out! Glad to have helped….

Reply | Quote