UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Attach Date To Transferspreadsheet To Excel, Access 2013    
 
   
ebroomhead
post Dec 26 2019, 12:59 PM
Post#1



Posts: 28
Joined: 19-May 15



This is the code that I would like to use to give my Excel file a date and time. However, when I use it I get the error message below.
If I exclude the zcbotext, ie date, it downloads the Excel file OK. Any assistance would be appreciated.

Dim ZFilename As String
Dim zqryout As String
Dim zcbotext As String
'Get the second column of the selected item in the combox
'if you have two columns then 1st = 0 most likely bound
'and you can simply call the dot-value of the control
'2nd = 1 and you either have to set focus or use this trick
zcbotext = Forms![inventory main menu]!Text94
'and now add these to the path I used before:
ZFilename = CurrentProject.Path & "\" & "Current Inventory" & zcbotext & ".xlsx"
'name of a stored query. Xfer Mthd will NOT use dynamic
zqryout = "tblinventorycurrent"
' and finally
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, zqryout, ZFilename, True
MsgBox "Excel file created in same directory as this MS Access programme.", vbOKOnly



Excerpt from error message:

Runtime error #3044

XXXXX is not a valid path. Make sure that the path ..................on which the file resides.




This the code that works but with no date in the downloaded Excel file.

Dim ZFilename As String
Dim zqryout As String
Dim zcbotext As String
'Get the second column of the selected item in the combox
'if you have two columns then 1st = 0 most likely bound
'and you can simply call the dot-value of the control
'2nd = 1 and you either have to set focus or use this trick
'zcbotext = Forms![inventory main menu]!Text94
'and now add these to the path I used before:
ZFilename = CurrentProject.Path & "\" & "Current Inventory" & zcbotext & ".xlsx"
'name of a stored query. Xfer Mthd will NOT use dynamic
zqryout = "tblinventorycurrent"
‘ and finally
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, zqryout, ZFilename, True
MsgBox "Excel file created in same directory as this MS Access programme.", vbOKOnly

Again, I would appreciate any help as the original code that won't add the date works for other programs that I have used.

Go to the top of the page
 
theDBguy
post Dec 26 2019, 01:30 PM
Post#2


UA Moderator
Posts: 77,499
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just a guess but try changing this line:
CODE
zcbotext = Forms![inventory main menu]!Text94

into this:
CODE
zcbotext = Format(Forms![inventory main menu]!Text94,"yyyy-mm-dd")
Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ebroomhead
post Dec 28 2019, 10:48 AM
Post#3



Posts: 28
Joined: 19-May 15



Thanks you, theDBguy, it worked.

Appreciate your assistance. hat_tip.gif
Go to the top of the page
 
theDBguy
post Dec 28 2019, 11:24 AM
Post#4


UA Moderator
Posts: 77,499
Joined: 19-June 07
From: SunnySandyEggo


Hi. You're welcome. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 05:11 PM