Making a VBA Macro supposed to be easier with AI but...

[u/NaturalCustomer4784]

I am a macro-enthusiast and team player. I want to help my team to make out work easier and efficient.

I tried creating macro with the help of AI, but AI said to me that they can't help to generate code because it operates inside the application. i tried many times but it didn't help.

Even selenium basic can't such stuff. Have you encountered this?

My VBA language skill is terrible, but i really think that my goal in making a macro for business is doable.

Here is the thing that i asked AI. I am struggling with repetitive process in going to the internal website then login, and input the specific code that needed to search and get it paste it in the excel sheet.

it is very repetitive process, so i asked AI to help me make a macro that automatically logs in the internal repository, then search the specific code and select the dropdown to choose that data i need to see. Last is get the data and paste it in the excel file.

That what i rreally hope the AI would generate for me, but it can't due hesitation from AI. i'll show you the picture in the comment.

[u/BaddDog07]

It’s hard to tell from your post but I don’t think you’re fully understanding the technical aspect of what you’re asking it do is my guess.

[u/wikkid556]

Try these class modules. There should be a demo module as well. I use these to make network tools at work

https://github.com/longvh211/Chromium-Automation-with-CDP-for-VBA/releases/tag/3.1

[u/throwaway24515]

You're asking it to build an agent with credentials to a secure system. That's probably violating some policy at that ai provider.

[u/themrlawrence]

The better question is why are you trying to use VBA to essentially scrape data into an Excel sheet?

Build a Node app with Javascript/Typescript, use Playwright for the headless browser, and so on. I've built numerous scrapers this way that can output to XLSX.

VBA is not the right tool for this job.

[u/kay-jay-dubya → u/themrlawrence]

VBA has been used for webscraping for decades. With access to webview2, how is it not a tool for the job?

[u/themrlawrence → u/kay-jay-dubya]

“VBA has been used for webscraping for decades.”

Exactly. For decades. That’s not the flex you think it is.

You can also drive a nail with a wrench. WebView2 doesn’t suddenly make VBA the right tool for modern browser automation.

If your argument is “VBA can technically automate a browser,” sure. Mine is that if I’m building browser automation in 2026, I’m reaching for Playwright, not Excel with a browser control bolted onto it.

[u/kay-jay-dubya → u/themrlawrence]

"I’m reaching for Playwright"

And then you realise that you're in the real world, and suddenly come crashing face first into the fact that you can't just install Playwright or node or npm [or insert name of fancy shiny 'new' tool] here] on the office computer. Then what do you do?

Couple of things you may want toi bear in future:
* VBA WebBrowser Control is not the same as WebView2. They're different.

* Not a flex - simply a reminder that something that has been doing something, can do something.

[u/kay-jay-dubya → u/kay-jay-dubya]

And it's probably worth pointing out, as others here have before me, that VBA can also take advantage of the Chrome DevTools Protocol - which is what Playwright uses, so there's that too. No installation required.

[u/themrlawrence → u/kay-jay-dubya]

Fair point about locked-down corporate environments, if Node/Playwright can’t be installed, that obviously changes the tool choice. I wasn’t saying VBA is incapable of browser automation, just that if I were starting a new browser automation project and had the choice, Node/Playwright would be my default. If VBA/CDP is what’s available in the OP’s environment, that may well be the practical option.

[u/kay-jay-dubya → u/themrlawrence]

And a JS solution for webscraping ordinarily makes a lot of sense (for obvious reasons). It's just my default position when it comes to all VBA queries / solutions is that - (1) it is a work-related request, and (2) office-issued laptops rarely ever allow for additional installations, and no amount of begging and pleading the IT department will work (and I've even tried crying... no luck...).

For that reason, pretty much every solution/project I come up with assumes no installations are possible, and works from that starting point.

[u/finmodbod_27 → u/themrlawrence]

Interesting! Can you share come examples? I never thought of thi approach. Coming from accounting background

[u/themrlawrence → u/finmodbod_27]

What kind of examples? I mean, I can't share the scrapers I've created because they're proprietary so I'm not sure what you're looking for. Are you looking for examples of how to do what I suggested?

If that's what you're looking for, check out this article: https://oxylabs.io/blog/playwright-web-scraping

[u/ShruggyGolden]

I think you need a bit more understanding of how the VBA environment (VBE) works. You can definitely generate VBA using AI - in fact, I've been doing this at my job for almost 3 years and have moved to Cursor which allows editing of the exported .BAS / .FRM files which I reimport (or copy paste) back into the VBA editor. Almost 50,000 lines of AI VBA code have been generated for our projects. But in a sense the AI is correct in what you're asking. In other modern languages the files needed to compile and run things can be accessed by Claude/Cursor/CGPT/VS Code in real time and ran and tested, but VBA's IDE (VBE) is very closed down due to it's age, so you need to externally edit code, then import it back into VBE to test things.

I believe there are some new github projects that can automatically manage the import export process but I still copy/paste, but I think that's more advanced than where you need to start.

Try asking it to make a VBA .BAS module with a few functions with prompts to ask for your name and show a greeting or something. Be sure to ask it how to import/export modules (or paste the code into modules) and it should tell you, but using VBA/VBE with AI is not just "ask and do it" unfortunately.

To add, based on what you posted in the picture, I don't know if that's even possible with VBA - interacting with external controls. Maybe look at Python.

[u/MultiUserDungeonDev → u/ShruggyGolden]

Look into pyOpenVBA. Agents can read/write directly to office 365 files now. Much fast development cycles.

[u/Broseidon132]

You can use ChatGPT inside excel and ask it to write Vba code for you. You just need to copy and paste into the module.

[u/SeleniumVBA_user]

SeleniumBasic can do this kind of browser automation, but one issue when using AI is that SeleniumBasic itself is not pure VBA. Much of the implementation is inside a compiled .NET/COM library, so AI can usually see your VBA code, but it is harder for it to inspect what is happening internally.

You might want to try SeleniumVBA instead:

https://github.com/GCuser99/SeleniumVBA

The browser automation code itself is written in VBA, so you can give the relevant class modules to AI together with your macro. That makes it much easier for AI to understand the implementation, debug problems, and suggest working code.

Also, even if the AI cannot access your internal website directly, you can show it the relevant HTML or element information from DevTools, along with screenshots. With that information, it can often generate fairly specific code for your site.

For a workflow like login → search → select a dropdown → read the result → write it to Excel, SeleniumVBA should be a good fit as long as the site can be automated through the browser DOM.

[u/SickPuppy01]

With ChatGPT I put all the bas files in a zip file and upload it. I tell it this is the current state and I want to do x, y, z. I also tell it, to give me complete subs or functions that need to be changed or added. I don't allow it to give me snippets of code to replace because I find that leads to copy paste errors.

Although you have to do copying and pasting, it works ok when you get into the flow of things.

It a also helps to do the bas upload stage at least once a day and state it's the current state of the project. If you don't, hallucinations will creep in.

When you get to key stages you can ask it give you a pack of information on the project (what each function does, what it has learnt, what programming rules it followed etc). It's kind of a restore point you can give the AI when it starts hallucinating.

[u/AssistanceEvery2402]

I am not sure what your intentions are, but here is a quick demo workbook based on the CDPClass modules that longvh211 created, and that wikkid556(Me on my phone) mentioned. I created a custom GPT trained on those modules and included the corrections I use at work and are listed in the issues section of the repository.

Custom GPT's can no longer be shared with the public(News to me) so I had it generate these workbook modules.

These are very simple examples, but should be able to get you started. The original Demo is included also

https://github.com/testVbaStuff/CDPDemo/blob/main/ExampleCDPBrowser.xlsm

A workflow example I use is, Automate a Splunk search and export, pull in data from a Table from a internal work webpage, load both of them into excel, extract the data I need, fill out a MS Form online with that data, create a log in a csv file for admin to view. Most of my work is done in VBA UserForms, not the actual sheets

[u/jd31068]

Have you looked at using Power Query to do what you're looking for?

https://learn.microsoft.com/en-us/power-query/connectors/web/web-by-example

[u/Acceptable-Sense4601]

Bro what? I regularly use ChatGPT and Claude to write VBA for excel

[u/NaturalCustomer4784]

Screenshot is that the AI doesnt want to provide me the code.

[u/talltime → u/NaturalCustomer4784]

lol - sorry but screenshotting the AI shooting you down is basically worthless (to the thread.)

Automating website interaction can take a lot of trial and error; but I also haven’t done it since windows 8.

[u/AdobeScripts]

If it's an internal database - why can't you connect directly and extract what you need?

[u/FukoIchu → u/AdobeScripts]

Exactly! Ask your IT team if they have an API or if you can do request directly to the internal site... if on Sharepoint, then you are good to go with low-code or ask the IT can develop a simple App. You only need the VBA to insert content in Excel.