[AccessD] Transfer Spreadsheet

Bill Benson bensonforums at gmail.com
Mon Oct 8 14:12:36 CDT 2018


I always store Excel templates in an attachment field in a table in my
Access databases. When I need to export recordsets to Excel, I spool the
file out to disk from the recordset2 type recordset (Attachment field), and
automate Excel to open the file and put data into it. I find this extremely
fast and portable and I am not reliant on any naming conflicts. I even run
the macro code in the excel templates from Access, knowing Access has 100%
ownership of an Excel instance and the file it needs to work with. Peachy!

On Mon, Oct 8, 2018 at 12:40 PM <accesspro at cox.net> wrote:

> All,
> Yes Access did not like my concatenation. Not sure if it was the slashes or
> spaces.
> OutputTo is working well now providing me a new file each time.
> I guess that is one of the differences between OutputTo and
> TransferSpreadsheet methods....
>
> Thanks Again,
>
> Bob
>
>
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Rocky
> Smolin
> Sent: Sunday, October 7, 2018 10:08 PM
> 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
>


More information about the AccessD mailing list