Send data to excel spreadsheet with OpenXML (1 Viewer)

Auntiejack56

Registered User.
Local time
Tomorrow, 04:12
Joined
Aug 7, 2017
Messages
90
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

I’m here to help
Staff member
Local time
Today, 11:12
Joined
Oct 29, 2018
Messages
15,653
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?
 

Auntiejack56

Registered User.
Local time
Tomorrow, 04:12
Joined
Aug 7, 2017
Messages
90
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
 

Isaac

Lifelong Learner
Local time
Today, 11:12
Joined
Mar 14, 2017
Messages
5,386
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

once i caught a fish alive...
Local time
Tomorrow, 02:12
Joined
May 7, 2009
Messages
13,328
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

Registered User.
Local time
Tomorrow, 04:12
Joined
Aug 7, 2017
Messages
90
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

once i caught a fish alive...
Local time
Tomorrow, 02:12
Joined
May 7, 2009
Messages
13,328
for free, compatible viewer/editor, you can use LibreOffice?
no lisence required.
 

Auntiejack56

Registered User.
Local time
Tomorrow, 04:12
Joined
Aug 7, 2017
Messages
90
LibreOffice. Hmm, that's a new track altogether but I'll have a look at it, thanks arnelgp
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:12
Joined
Oct 29, 2018
Messages
15,653
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

Registered User.
Local time
Tomorrow, 04:12
Joined
Aug 7, 2017
Messages
90
Thanks DBGuy and all for your attention and suggestions.

Jack
 

Auntiejack56

Registered User.
Local time
Tomorrow, 04:12
Joined
Aug 7, 2017
Messages
90
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?
 

arnelgp

once i caught a fish alive...
Local time
Tomorrow, 02:12
Joined
May 7, 2009
Messages
13,328
can you settle for an older version (xls).
the demo create the old xls (not XML) file.
 

Attachments

  • CreateXLS.accdb
    436 KB · Views: 22

Auntiejack56

Registered User.
Local time
Tomorrow, 04:12
Joined
Aug 7, 2017
Messages
90
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
 

Users who are viewing this thread

Top Bottom