I've got an Access runtime on a machine without Office installed.

I want to export data to an xlsx file, but all the builtin methods - TransferSpreadsheet, OutputTo, SQL directed to Excel file, ADODB connection to Excel, table built in Excel - are either dependent on an Excel object, or use a driver that is disabled by
Microsoft in the current version.

It seems the only 2 options I have are:
Install Excel, or
Find a DLL that will do the job for me, or
Find some very fancy VBA code.

Well, there are tonnes and tonnes of solutions in C# and .NET out there, but none of them are available to VBA. IronXL, NPIO, SpreadsheetLight, ClosedXML: they all have DLLs but are not COM enabled as far as I can tell.

Does anybody know of an out-of-the-box solution that I can use in VBA?

(BTW sorry I haven't posted here since 2009 - been busy).

Thanks,

Jack

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Auntie Jack formulated the question :

I've got an Access runtime on a machine without Office installed.

I want to export data to an xlsx file, but all the builtin methods - TransferSpreadsheet, OutputTo, SQL directed to Excel file, ADODB connection to Excel, table built in Excel - are either dependent on an Excel object, or use a driver that is disabled by Microsoft in the current version.

It seems the only 2 options I have are:
Install Excel, or
Find a DLL that will do the job for me, or
Find some very fancy VBA code.

Well, there are tonnes and tonnes of solutions in C# and .NET out there, but none of them are available to VBA. IronXL, NPIO, SpreadsheetLight, ClosedXML: they all have DLLs but are not COM enabled as far as I can tell.

Does anybody know of an out-of-the-box solution that I can use in VBA?

(BTW sorry I haven't posted here since 2009 - been busy).

Thanks,

Jack

Third option: Export to a CSV. You can use TransferText to do it.

DoCmd.TransferText acExportDelim, , [QueryName], [OutfileName], True

Excel is pretty accommodating with CSV files, so in most cases this
will come down to a teeny, tiny bit of user training and you are good
to go. On my PC Excel is the Default app for CSV files. I am not sure
if that is some setting I made years ago or is a standard thing with
default Office installations.

--
This email has been checked for viruses by AVG.
https://www.avg.com

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Thanks for the suggestion Ron! Can't go with CSV unfortunately, as we will be given some Excel templates by clients which we'll be dumping data into.

Also, I like your 'teeny, tiny bit of user training'! We won't know most of our users, so even a teeny tiny bit of training is too much!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Auntie Jack wrote :

Thanks for the suggestion Ron! Can't go with CSV unfortunately, as we will be given some Excel templates by clients which we'll be dumping data into.

Also, I like your 'teeny, tiny bit of user training'! We won't know most of our users, so even a teeny tiny bit of training is too much!

In this case you might want to attack the problem from the Excel side,
and allow the users with Excel to suck data out of the Access database.
It's obvious that the user(s) on the computer(s) without Excel will
not be able to view or manipulate the data.

Rdub

--
This email has been checked for viruses by AVG.
https://www.avg.com

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Jack,

Am 22.04.2021 um 13:29 schrieb Auntie Jack:

I've got an Access runtime on a machine without Office installed.

I want to export data to an xlsx file, but all the builtin methods - TransferSpreadsheet, OutputTo, SQL directed to Excel file, ADODB connection to Excel, table built in Excel - are either dependent on an Excel object, or use a driver that is disabled

by Microsoft in the current version.

It seems the only 2 options I have are:
Install Excel, or
Find a DLL that will do the job for me, or
Find some very fancy VBA code.

Well, there are tonnes and tonnes of solutions in C# and .NET out there, but none of them are available to VBA. IronXL, NPIO, SpreadsheetLight, ClosedXML: they all have DLLs but are not COM enabled as far as I can tell.

Does anybody know of an out-of-the-box solution that I can use in VBA?

if you obviously want to make money with your application and need Excel
to do so, why look for third party vendors instead of purchasing a
license for Excel?

Ulrich

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Ulrich Möller formulated on Friday :

Hi Ron,
would you please turn off the advertising for AVG in your replies?

Thanks

Ulrich

Hmmmm... I didn't even notice that AVG was attaching their blurb to out
going posts. I rummaged around in the AVG and think I found and turned
off the setting that seems to be annoying you. I guess after I hit
send we'll find out for sure.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Ron,
would you please turn off the advertising for AVG in your replies?

Thanks

Ulrich

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hallo Ron,

Am 24.04.2021 um 01:38 schrieb Ron Weiner:

Ulrich Möller formulated on Friday :

Hi Ron,
would you please turn off the advertising for AVG in your replies?

Thanks

Ulrich

Hmmmm... I didn't even notice that AVG was attaching their blurb to
out going posts.  I rummaged around in the AVG and think I found and
turned off the setting that seems to be annoying you.  I guess after I
hit send we'll find out for sure.

Looks like the blurb is gone now. Thanks a lot!

Ulrich

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, April 23, 2021 at 4:22:13 PM UTC-5, Ron Weiner wrote:

Auntie Jack wrote :

Thanks for the suggestion Ron! Can't go with CSV unfortunately, as we will be
given some Excel templates by clients which we'll be dumping data into.

Also, I like your 'teeny, tiny bit of user training'! We won't know most of
our users, so even a teeny tiny bit of training is too much!

In this case you might want to attack the problem from the Excel side,
and allow the users with Excel to suck data out of the Access database.
It's obvious that the user(s) on the computer(s) without Excel will
not be able to view or manipulate the data.

Rdub
--
This email has been checked for viruses by AVG.
https://www.avg.com

If the template is a simple table like worksheet with or without headers; try linking to the file and edit it like a table. I don't know what will happen to formatting or formulas. Otherwise you could try installing a 3rd party spreadsheet like
OpenOffice and use it's API.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)