Any Excel automation mavens in the audience? I'm trying to automate what Excel does when you do 'Get Data' from Text\CSV via PowerShell. I've recorded that process as a macro and the code in the linked Gist is attempting to replicate the macro VBA.
However no matter what I do it throws a 1426 COM error '800a03ec: Unknown COM status code' when attempting to add to the Workbook ListObjects collection.
https://learn.microsoft.com/en-us/office/vba/api/excel.listobjects.add
Probably something stupid - can anyone see any issue with the code ?
https://gist.github.com/AlanPBourke/091e4aa607239fa69d5f544e31bc958b
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/40b5a164-1c7f-4058-8a65-5017af0fa3be@app.fastmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Hey Alan,
Lately I have been spending a LOT of time with my buddy Claude. Like a LOT. Mostly for work - including FoxPro, converting FoxPro to C# and even working on reports using QuestPDF & C#.
So, my suggestion - have you tried hitting up either Claude.ai (with Free access) - or even CoPilot - with your question. Claude is more coding centric, but, I've indeed did initial FoxPro coding conversion testing with CoPilot.
Anyway - it's just a thought...
-K
________________________________
From: ProFox <profox-bounces@leafe.com> on behalf of Alan Bourke <alanpbourke@fastmail.fm>
Sent: Friday, June 13, 2025 6:46 AM
To: profoxtech@leafe.com <profoxtech@leafe.com>
Subject: Automatic Excel Power Query CSV Import
Any Excel automation mavens in the audience? I'm trying to automate what Excel does when you do 'Get Data' from Text\CSV via PowerShell. I've recorded that process as a macro and the code in the linked Gist is attempting to replicate the macro VBA.
However no matter what I do it throws a 1426 COM error '800a03ec: Unknown COM status code' when attempting to add to the Workbook ListObjects collection.
https://learn.microsoft.com/en-us/office/vba/api/excel.listobjects.add
Probably something stupid - can anyone see any issue with the code ?
https://gist.github.com/AlanPBourke/091e4aa607239fa69d5f544e31bc958b
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/DM6PR13MB3852EB4D39D319D62D69F270C377A@DM6PR13MB3852.namprd13.prod.outlook.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
IIRC that's a pretty generic automation error code. One thing I recall from when I was doing more COM stuff was that VFP could lose track of object references particularly inside loops. Your code doesn't seem to be doing that sort of thing but that's my first thought.
Have you tried trying to do this interactively from the VFP command window? Generally speaking I would prototype COM stuff in the command window until I got things almost exactly perfect. 😊
--
rk
-----Original Message-----
From: ProfoxTech <profoxtech-bounces@leafe.com> On Behalf Of Alan Bourke
Sent: Friday, June 13, 2025 6:47 AM
To: profoxtech@leafe.com
Subject: Automatic Excel Power Query CSV Import
Any Excel automation mavens in the audience? I'm trying to automate what Excel does when you do 'Get Data' from Text\CSV via PowerShell. I've recorded that process as a macro and the code in the linked Gist is attempting to replicate the macro VBA.
However no matter what I do it throws a 1426 COM error '800a03ec: Unknown COM status code' when attempting to add to the Workbook ListObjects collection.
https://learn.microsoft.com/en-us/office/vba/api/excel.listobjects.add
Probably something stupid - can anyone see any issue with the code ?
https://gist.github.com/AlanPBourke/091e4aa607239fa69d5f544e31bc958b
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/MW4PR10MB65840D306D57B093CE8C0246D277A@MW4PR10MB6584.namprd10.prod.outlook.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Alan:
It's only been 25 years since I've tried office automation, but, ...
Why are you using XLSRCEXTERNAL? I may be reading the MS page wrong,
but have you tried using xlSrcQuery since you're specifying an OLEDB
connection string?
On Fri, Jun 13, 2025 at 6:47 AM Alan Bourke <alanpbourke@fastmail.fm> wrote:
>
> Any Excel automation mavens in the audience? I'm trying to automate what Excel does when you do 'Get Data' from Text\CSV via PowerShell. I've recorded that process as a macro and the code in the linked Gist is attempting to replicate the macro VBA.
>
> However no matter what I do it throws a 1426 COM error '800a03ec: Unknown COM status code' when attempting to add to the Workbook ListObjects collection.
>
> https://learn.microsoft.com/en-us/office/vba/api/excel.listobjects.add
>
> Probably something stupid - can anyone see any issue with the code ?
>
> https://gist.github.com/AlanPBourke/091e4aa607239fa69d5f544e31bc958b
>
> --
> Alan Bourke
> alanpbourke (at) fastmail (dot) fm
>
>
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/CACW6n4vudgXEwQ0+_OQDw1y9QLW7x0YHhS5-E3q7eg_1NbBcAA@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
I haven't, I will give that a try thanks. I was just replicating what the Excel Macro recorder spits out, TBH
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/05c655f5-3608-43fa-bfab-00a5c93953d4@app.fastmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Yeah, works OK from the command window. It adds the query string correctly into the Workbook queries, it's something to do with adding a named Table that pulls from the query.
If the worst comes to the worst I can just inject the data into the sheet with ADODB ...
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/fe4ad217-a41b-488a-a4d5-4e9275c8035b@app.fastmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Fri, 13 Jun 2025, at 3:53 PM, Alan Bourke wrote:
> Yeah, works OK from the command window. It adds the query string
Correction - it does NOT work OK from command window.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/e85222ba-2656-4fcc-8924-73daf5e16059@app.fastmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Author: <foxdev@ozemail.com.au>
Posted: 2025-06-13 17:24:54 Link
Maybe the constant - #DEFINE XLSRCEXTERNAL 0 - should it be 4 ?
SourceType
. 1 = xlSrcRange
. 2 = xlSrcXml
. 3 = xlSrcQuery
. 4 = xlSrcExternal
-----Original Message-----
From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Alan
Bourke
Sent: Saturday, 14 June 2025 12:55 AM
To: profoxtech@leafe.com
Subject: Re: Automatic Excel Power Query CSV Import
On Fri, 13 Jun 2025, at 3:53 PM, Alan Bourke wrote:
> Yeah, works OK from the command window. It adds the query string
Correction - it does NOT work OK from command window.
--
Alan Bourke
alanpbourke (at) fastmail (dot) fm
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/006301dbdcb1$fd421490$f7c63db0$@ozemail.com.au
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.