VBA question

Haych

It is many years since I had cause to delve into VBA, but I used to quite enjoy it.

That said, I'm well rusty, so I have a query I hope one of you wonderful cleverer people can answer for me.

I have one Excel VBA enabled file with a bunch of subroutines in that I'm going to call and run in sequence, probably from a button on a sheet in the file.

However, I want the code to be executed on a different file completely.

How do I do this?

It's just basic formatting, removing blank rows, renaming headers and unmerging cells, etc.  The code I've written works, I just need to figure out how to point the code at, and execute, on a different file.

Ta much.

Sporky

Assuming the other file is open, you can create a workbook object that references it (let's assume you call it target) , then all of your current code just changes from thisworkbook. or (bleagh) activeworkbook. to targetwb.

Sporky

Or move/copy the code into your personal workbook and run it from there. 

Haych

Hmm, I'm leaning towards "just copy that sheet into the VBA workbook" lol.

Sporky

Or the code into the other workbook? 

Haych

Sporky said:

Or the code into the other workbook? 

I think that will be too much for them to handle, much easier to get them to Ctrl-A, Ctrl-C, Ctrl-V

Sporky

Ah - I didn't realise you were going to expose it to users!

I'd write an add-in. 

Haych

Sporky said:

I'd write an add-in. 

You may as well have written that in Japanese.  I'm only slightly clever, I already know you are much more competent than I with stuff like this.

notanon

Try ChatGPT you'll need to modify the first answer no doubt but should get there. Security is usually the biggest blocker for VBA.

Consider powershell or vbscript, again ChatGPT may help.

Good luck!

Sporky

Haych said:

Sporky said:

I'd write an add-in. 

You may as well have written that in Japanese.  I'm only slightly clever, I already know you are much more competent than I with stuff like this.

Ah, OK. 

The I'd make a workbook with the current code in it, and a button. The button fires the file open dialogue box, then runs the code on whatever is opened. 

snowblind

notanon said:

Try ChatGPT you'll need to modify the first answer no doubt but should get there. Security is usually the biggest blocker for VBA.

Consider powershell or vbscript, again ChatGPT may help.

Good luck!

Seconded. Macros are pretty much anathema to Micro$oft these days so converting to powershell would be the preferred solution. If your business site is sufficiently security conscious (paranoid) they may block all macro activity via policy.

You can probably get OpenAI etc to write the script for you in a few seconds. As mentioned it may take a couple of iterations but it is generally pretty good if you phrase the description of the requirement accurately.

Sporky

Can you use PS inside Excel?

I suppose you could possibly write something to edit the XML underlying the spreadsheet, but that feels like trying to fix a leaking tap through interpretative dance.

Ive tried ChatGPT for VBA, a D the results were mixed at best. It managed a simple task (is this range within or overlapping this range) but with inelegant code. It's attempt to populate an array from a filtered table was just wrong, and even by the third attempt it was still using an RBAR approach. 

This is a relatively simple problem by comparison - the core code is written, it just needs to be pointable at other files.