Visual Basic or some kind of script help

[Graeme NZ]

A client has sent through an extremely advanced MS excel file - I can't upload it for privacy reasons, but it's basically a spreadsheet that generates a report with graphs, charts and data based on what you select from a from a drop-down list. I select "X" from the list and a swanky report with dynamic graphs and charts is displayed in the cells below. I then click file>print using the most recently chosen print settings and out pops a 4 page report from the printer. Then I select the next item on the list and print, and so on and so forth.

I was wondering is there some way to create a Visual Basic (VB) script or macro (or something) to save me having to repeat that process? I spent 6 hours yesterday repeating the process it 535 times and just about lost my mind... Could someone point me in the direction for me to learn some basic VB, (assuming that VB is the answer and that what I'm asking for is actually basic)? I have zero experience in VB so I'd need to start at the very beginning, but my mind is wired in a way that it wouldn't take too long to understand the basics.

It would be nice if the VB script is separate from the excel spreadsheet as there is a chance that I'd have to run that script over several different files. This is to cover the possibility of a rerun or getting a new file with similar content.

I've heard that there's a distinct possibility that this job will come again but with much larger quantities. I'm getting nervous that I'm going to be chained to my desk for weeks on end repeating each step thousands of times...

I'm sure there is better software out there to actually generate the report itself, but since the client has already done the hard work it makes sense to me that we work with their file instead of re-inventing the wheel so that it's easier to print. In any case, we don't have that software (we use Print Shop Mail and/or InDesign) and it would probably take just as much time to recreate the file as it would to print the clients.

Any help would be appreciated!

 

[ssutton503]

I have done a fair amount of programming with Visual Basic in Microsoft Access and a little in Excel. While I studied Basic programming back in college, my VB experience has come from books I purchased or online sources. I have a copy of Excel 2003 Bible here and it has a chapter on using VB. I would suggest you search for books on programming VB for Excel or even just an Excel book which may contain a VB chapter. You should also do a search for online VB classes. There are lots! Even Microsoft offers lessons on VB for Excel.

Will VB be your answer? I think it will do what you want. I would think you could create a loop to cycle through the popup list options and then print. I don't know if you can create a script like this that would be independent of the actual document but it would be easy enough to copy the script, paste into the new document and run it.

Hope this helps a little.

Steven

A client has sent through an extremely advanced MS excel file - I can't upload it for privacy reasons, but it's basically a spreadsheet that generates a report with graphs, charts and data based on what you select from a from a drop-down list. I select "X" from the list and a swanky report with dynamic graphs and charts is displayed in the cells below. I then click file>print using the most recently chosen print settings and out pops a 4 page report from the printer. Then I select the next item on the list and print, and so on and so forth.

I was wondering is there some way to create a Visual Basic (VB) script or macro (or something) to save me having to repeat that process? I spent 6 hours yesterday repeating the process it 535 times and just about lost my mind... Could someone point me in the direction for me to learn some basic VB, (assuming that VB is the answer and that what I'm asking for is actually basic)? I have zero experience in VB so I'd need to start at the very beginning, but my mind is wired in a way that it wouldn't take too long to understand the basics.

It would be nice if the VB script is separate from the excel spreadsheet as there is a chance that I'd have to run that script over several different files. This is to cover the possibility of a rerun or getting a new file with similar content.

I've heard that there's a distinct possibility that this job will come again but with much larger quantities. I'm getting nervous that I'm going to be chained to my desk for weeks on end repeating each step thousands of times...

I'm sure there is better software out there to actually generate the report itself, but since the client has already done the hard work it makes sense to me that we work with their file instead of re-inventing the wheel so that it's easier to print. In any case, we don't have that software (we use Print Shop Mail and/or InDesign) and it would probably take just as much time to recreate the file as it would to print the clients.

Any help would be appreciated!

 

[Digital Press Guy]

Given the hands on intensive nature required to print this job it may not make as much sense as you say to continue to use the customer's Excel sheet. I would think that they've put this report together from a print it at the desktop perspective. Not from a production perspective.

VDP solutions such as Jet Letter's PageBuilder can import data from multiple files, make the same sort of calculations that Excel does and then create charts and graphs from that data. While 535 records is not a huge amount you would be looking at a merge or minutes versus the six hours it took you.

Good Luck.

 

[seratne]

I would look into this: https://www.autoitscript.com/site/autoit/
Might be the easiest way to go about it. The run time will probably be longer, but at least it can be unattended, while the script clicks away for you.

 

[hjzhang527662]

Depending on your job, you may write your own VBScript code to solve this problem is too hard, but do not write VBScript code manually do, the more tired. The same operation is repeated.

I recommend that you use variable data printing software, such as: UCCSOFT of SmartVizor variable data printing software or GMC's PrintNet. Take SmartVizor is concerned, it supports variable charts. You can use the following link to download a trial version, http://www.uccsoft.com. You can also extract your MS Excel file a small part, including the data itself, and you need to generate results, sent to [email protected] via e-mail, they will be free to design templates for you within 24 hours, and write how to design a document template. Of course, you can also get more help from the GMC or OL.

 

[Graeme NZ]

Thanks for your input - I'll look into the various suggestions and see what ones I prefer.

Either way, it looks like I'll be off to the library sometime soon and hunting out an excel/VBA book. Up skilling never hurt anyone eh?

Chur,

Graeme