[AccessD] Transfer Spreadsheet

[Stuart McLachlan]

Yes, the spaces after "...TimeOutput" and "PayPeriod" mean that the whole string needs to 
be passed surrounded by double quotes.

 strFileName = """" & "C:\A-Best Systems\TimeOutput " & strDateToday & " PayPeriod "
 & strJobPeriod & ".xlsx" & """"

(Also as pointed out in a previous post, you need to avoid any "/"'s in strDateToday.


On 8 Oct 2018 at 11:47, James Button via AccessD wrote:

> Space (as in " PayPeriod ") or special chars in the name means you have to bound
> the fullname in "
> And as the entry is a text stream, that would probably be doubled " at the start
> and end 
> 
> JimB  
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Rocky Smolin
> Sent: Monday, October 8, 2018 6:08 AM
> To: 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Transfer Spreadsheet
> 
> Might be something wrong with your appends to make the file name.
> 
> I suggest you make the file name in a string variable
> strFileName = "C:\A-Best Systems\TimeOutput " & strDateToday & " PayPeriod "
> & strJobPeriod & ".xlsx"
> 
> Then take a look at it
> MsgBox "*: & strFileName & "*"
> 
> Maybe you'll see something there that the OS doesn't like.  Something that
> violates the file naming conventions, like one of a forbidden character for
> file names.
> 
> Maybe it's in strDateToday. File name can't have |,?,:,*,",>,<,\,or /.
> 
> HTH
> 
> 
> Rocky Smolin
> Beach Access Software
> 760-683-5777
> www.bchacc.com
> www.e-z-mrp.com
> Skype: rocky.smolin
>  
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> accesspro at cox.net
> Sent: Sunday, October 07, 2018 9:30 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Transfer Spreadsheet
> 
> To be clear the method works, just not able to create a new excel file......
> 
> This Works as long as I have the excel file in the folder:
>  'DoCmd.TransferSpreadsheet acExport, 10, "tblTimeCard", "C:\A-Best
> Systems\TimeOutput.xlsx", True
> 
> None of these will work. I guess because the file does not exist:
> DoCmd.OutputTo acOutputQuery, "qryOutputOldLargeReorderDays", acFormatXLSX,
> "C:\A-Best Systems\TimeOutput " & strDateToday & " PayPeriod " &
> strJobPeriod & ".xlsx"
> DoCmd.TransferSpreadsheet acExport, 10, "qryOutputOldLargeReorderDays",
> "C:\A-Best Systems\TimeOutput " & _
>     strDateToday & " PayPeriod " & strJobPeriod & ".xlsx", True
> 
> 
> tia
> 
> bob
> 
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Darryl
> Collins
> Sent: Sunday, October 7, 2018 8:42 PM
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Transfer Spreadsheet
> 
> Any chance you can post the relevant code segment? 
> 
> You should be able to do this seamlessly so curious to see what is going on.
> 
> Cheers
> Darryl.
> 
> 
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of
> accesspro at cox.net
> Sent: Monday, 8 October 2018 2:11 PM
> To: 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com>
> Subject: [AccessD] Transfer Spreadsheet
> 
> I am using the transfer spreadsheet method and am able to successfully
> export my data.
> What would be a bonus is for me to create a new excel file each time with a
> unique file name.
> When I include such vba code, I get an error "Not a valid file name".
> I want to NOT use my existing "template" excel file.
> Maybe not possible with this method ??
> 
> TIA
> 
> Bob
> 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>