VBA-trackworld

Hopefully someone can point me in the direction of some base code that I can potentially tweak. My VBA skills are pretty rusty these days and I certainly couldn’t write something from scratch

I have an excel pricing model. This model gets opened, 4 parameters are entered (client name, a date, my name, another person’s name) in 4 cells. A first macro is ran that pulls in a bunch of external data based on these parameters. Once that macro is ran, another one is ran that pulls in more data. Once that’s done, the file is saved as CLIENT NAME.xlsm in a certain folder

We have to do this 200+ times each month, sometimes 400+ times

I want to build some kind of batch tool where each month I create a table in an excel file, enter the 200 client names, the date for each client, and the 2 names. I then run a bit of code that opens up the base model, looks at row 1 of my table, runs the process above, then looks at row 2, does the above, etc etc until all the table has been ran

We could run this overnight rather than sitting all day with excel hogged while the current process runs

Anyone point me in the direction of some base code for something like this?

Isn’t where there a way to record a macro/VBA from doing it manually? I thought there was (it has been a while).

can gpt help?

That’s what I always used to do when messing with VBA.

Record a macro and examine the code and then edit as required.

This was nearly 30 years ago mind!

chatgpt is good at doing code

ahh, too slow

Isn’t where there a way to record a macro/VBA from doing it manually?

Ah, that’s not  a bad shout actually! Off to try that…

If someone were asking me to do this from scratch from what you’ve described (which I appreciate they aren’t in the case) I’d be looking at MS Power Automate to do this.

RM.

Yeah I would (and indeed do) use AppleScript (on a Mac obvs – guessing MSPA is the windows imitation of this 😉 ) for stuff like this. ChatGPT provides very useful assistance also!

Seconding the MS Power Automate suggestion.

You need a For-Next loop to loop through all the 200 clients

Each loop you read in the information from a cell, using either the Range object, or better still the Cells object.

e.g.

myvalue = Cells (1, 5).Value

would read in the info from row 1, column 5, which is cell E1.

If you need to look in a different row each time, then use a variable.

For x = 1 to 10

myvalue = Cells (x, 5).value

Next x

will read in the values from cell E1, then E2,then E3 and so on.

If you send me a simplified/dummy/non-critical dataset i can write a macro that gets you started.

I teach this shit 😉 …

chatgpt is good at doing code

Just to say, chatgpt is terrible at VBA – especially when it is built into Excel as it often gets mixed up with the versions of VBA in Access which doesn’t always have the same functionality available.  It might work but don’t beat yourself up if the code doesn’t behave as advertised as it is often wrong (for VBA that is, it might be great in other languages)

Edit: Is the idea that you then create 200 odd excel files based on the lines in your original excel file?  It sounds easy enough depending on where you want to pull in the external info from.  Can you expand a bit on that?  If you want to do it in VBA I can probably help although I usually work in Access.  If you want to go down the script route it sounds as though others here can help

MS PowerAutomate is awesome but the initial learning curve can be a bit horrible.  It’s worth it though

Rather than keeping it in Excel I’d look at putting this into a Google Sheet and letting Apps Script do the work on a nightly schedule.

I use Apps Script to create a number of Sheets based stuff on a daily basis and it works very well indeed.

(Also let AI or Reggie create the basics script that you can alter/learn from)

or outsource it to Fiverr or Upworker etc, where some Indian teenager will write the code for you for $5.

https://www.fiverr.com/

Tried Power Automate. First time I’ve used it and tried to record the process with that but it only seems to record the clicking around on sheets that I did. Wouldn’t record me running macros?

Anyway, I did the suggestion of recording a macro and going through the process to create one one file. I’ve got that working and I’ve tweaked the code to get it to do everything I need for one iteration. I just need to get it to work for however many I need it to run.

Basically each month were using this file to create a price for an individual client. This takes up a ridiculous amount of my teams time and the basic process is put the client identifying number in, put the required date in, stick the client managers name in and stick your name in. You then run a macro that goes to a data warehouse and pulls in lots of information about that client. You then run another macro that goes and runs some SAS reports and pulls that data into the file. Once that’s done you save the file as Client Name Month Number.xlsm, then move onto the next one. That whole process completely hogs your excel, so you can’t do anything else while it’s running, and each person in my team could have 40-50 of these to do in a couple of days. It’s a ridiculously inefficient use of time. In an ideal world we do this on a virtual machine or using some other fancy tech but in the immediate now it has to be done on our laptops. It’s the same every month, the same 4 parameters they need to enter, the same 2 macros they need to run. My short term solution is this batch process that says use this little table with the 4 parameters and 50 different clients you have to run, and do it at 5pm so they’re waiting ready in the morning

Going by your description, are you using Power Automate desktop or the Automate feature in excel? I was more thinking of the cloud service (it used to be called MS Flow) for this, however… the cloud service would need access to one drive, your data warehouse and the SAS reporting service to do this. And, as pointed out  previously, the learning curve for Power Automate can be frustrating but once you get the knack of things it can be really useful. Perhaps a long term plan!

Good Luck with getting this sorted, things like this drive me mental at work!!

RM.

So, you start with a table of the four parameters for all the clients, then you run your new macro with a For-Next loop.

You’ll need to set up parameters like where the file is going to be saved, but you can reference that to another cell, you’ll also need some kind of naming routine as I presume all 200 files will need to be different.

You’ll need to Dim your workbooks as a String and then populate it with the filename so that you can reference them in your macro and shift between files, for the likes of the new files that also means you can use the macro to update the filename. (eg Dim wbfile as String and then in the macro wbfile = ActiveWorkbook.Name)

So first off you’d do a count of the amount of rows in your data, then you’d use that as the final number in your loop. (For rows = 1 To rowcount)

Best way to do it is to break it down into chunks and have lots of little macro’s, then your main one for the whole thing just calls the subs as required. eg Call macro1

VBA’s really powerful though, I had a report that’d take ages to send different bits to different people, spent some time on a macro and had it copying out the data, saving it as a new file and then creating an email to each recipient, attaching the file and writing a standard blurb. Stopped short of it hitting send so I could add a bit of personalisation to it.

debug.print is your friend, as is writing Option Explicit at the top, but mainly just Google how to do the code – that’s mainly what I do.

IT won’t be the best code, but a combo of record and edit, plus googling (MrExcel.com is good for VBA code) should get you there.