[AccessD] Transfer Spreadsheet

James Button jamesbutton at blueyonder.co.uk
Mon Oct 8 05:47:43 CDT 2018


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



More information about the AccessD mailing list