Send data to excel spreadsheet with OpenXML

[Auntiejack56]

Hi,
I'm running an access runtime on prod machines without Excel, and no hope of getting Excel.
I normally create reports and dump data from the access app into Excel, but obviously the normal CreateObject("Excel.Application") ain't gonna woik.

So it looks like OpenXML might be the go; I could use it to populate an Excel template. Does anybody know whether the OpenXML SDK has the components I need, and can I reference them from VBA?

Thanks,

Jack

 

[theDBguy]

Hi Jack. When you normally dump Access data into Excel, are you populating a pre-formatted worksheet? Or, are you just exporting the data using TransferSpreadsheet or CopyFromRecordset?

 

[arnelgp]

and no hope of getting Excel.

why is that?

 

[Auntiejack56]

DBGuy, I create a new worksheet and populate it using CopyFromRecordset. Are you going to tell me that TransferSpreadsheet works regardless of whether Excel is installed?

arnelgp, cost of licences.

Jack

 

[arnelgp]

arnelgp, cost of licences.

can't use "shared" sites, torrents, cracked apps?

 

[Isaac]

Not sure how helpful my comment it going to be, but....I know there are 100 and 1 ways of creating Excel files without local Excel being installed in .Net. Maybe you could create a COM dll and then reference it in VBA - or download a ready made one from the 'net, which there are probably dozens.

 

[arnelgp]

why not export it as Text/CSV.
and view it on a PC with Excel.

if you are going to View it on same production pc with
only runtime, then whats the point of exporting it when
you can View it on Access on report or Query.

 

[Auntiejack56]

Isaac, yep may have to go down that route but I have no .Net skills so trying to avoid it if possible. Mind you, if you were correct about there being a readymade COM dll, I'd be doing it like a shot, but I haven't located one yet.

arnelgp, true, exporting it as csv may be my answer, but there would be no formatting capability, and a limit to single sheets, so would prefer to be able to manipulate the Excel file itself.

 

[arnelgp]

for free, compatible viewer/editor, you can use LibreOffice?
no lisence required.

 

[Auntiejack56]

LibreOffice. Hmm, that's a new track altogether but I'll have a look at it, thanks arnelgp

 

[theDBguy]

DBGuy, I create a new worksheet and populate it using CopyFromRecordset. Are you going to tell me that TransferSpreadsheet works regardless of whether Excel is installed?

arnelgp, cost of licences.

Jack

Hi Jack. Just to complete my thought, I was also going to suggest creating a CSV file, if you weren't using an Excel template.

 

[Auntiejack56]

Thanks DBGuy and all for your attention and suggestions.

Jack

 

[Auntiejack56]

Not sure how helpful my comment it going to be, but....I know there are 100 and 1 ways of creating Excel files without local Excel being installed in .Net. Maybe you could create a COM dll and then reference it in VBA - or download a ready made one from the 'net, which there are probably dozens.

Hi Isaac, Your comment has been very helpful, but I don't know how to implement the .Net solutions that I've found. NPIO and ClosedXml look like the most likely candidates, but their DLLs don't seem available to VBA. The trouble is, I have no VS skills so even if I build the DLL, which I did for NPIO, I don't know how to reference it - I couldn't find it when Browsing from Tools/References. Any ideas on how I could get traction on this?

 

[bastanu]

Looks like your original option would be fairly easy to implement:

How to: Create a spreadsheet document by providing a file name (Open XML SDK)

Use the Open XML SDK to programmatically create Office Word, Excel, and PowerPoint documents, and manipulate their content.

docs.microsoft.com

Cheers,

 

[Auntiejack56]

Looks like your original option would be fairly easy to implement:

How to: Create a spreadsheet document by providing a file name (Open XML SDK)

Use the Open XML SDK to programmatically create Office Word, Excel, and PowerPoint documents, and manipulate their content.

docs.microsoft.com

Cheers,

Hi,
I checked your link, and maybe you're right, it might be the simplest option. I've downloaded the SDK - can you advise how to reference it in VBA?
Thanks,
Jack

 

[arnelgp]

can you settle for an older version (xls).
the demo create the old xls (not XML) file.

 

[Auntiejack56]

can you settle for an older version (xls).
the demo create the old xls (not XML) file.

Woohoo, that is very cool!
I can use this for the time being, although I have to ensure xlsx is supported in future, so the search continues.
Thanks very much.
Jack