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
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]
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
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 ...
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:
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]
I haven't, I will give that a try thanks. I was just replicating what the Excel Macro recorder spits out, TBH
-
Alan Bourke -
foxdevï¼ ozemail.com.au -
Kurt Wendt -
Richard Kaye -
Ted Roche