topic Automating against Microsoft Access in Product Forum

Automating against Microsoft Access

cvmac — Fri, 03 Apr 2020 09:45:00 GMT

Hello,

We have an automation opportunity where excel files are required to be opened in Microsoft Access as they are too large for excel.

Can anyone offer any advice on how we can automate against Access?

Thanks,

------------------------------
Chloe MacIntosh
Automation Developer
Zurich Insurance
Europe/London
------------------------------

RE: Automating against Microsoft Access

John__Carter — Fri, 03 Apr 2020 15:42:00 GMT

Hi Chloe - if possible try to avoid spying the Access UI and instead query the data directly using something like OLEDB. Although I've never tried it, I'd imagine there is a way of silently importing the data from Excel into Access too, maybe via the command line or with some VBA code.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: Automating against Microsoft Access

david.l.morris — Fri, 03 Apr 2020 22:15:00 GMT

As John noted, OLEDB is the right way to go for getting data out of Excel without actually opening Excel. But you shouldn't need Access at all. You can query Excel using OLEDB. When you say 'excel files', are you saying that they're .xlsx/.xls files? How is it possible for the file to be too big to open in Excel? How large are these files? How many rows/columns/characters?

------------------------------
Dave Morris
Cano Ai
Atlanta, GA
------------------------------