How to print multiple workbooks in batch but allow for time for Bloomberg to pull data?

**goodquestionmma** · 02-03-2020, 11:16 AM

At the moment I have around 20 spreadsheets in a folder tree that includes one parent, three children and three grandchildren.

The names of the children and grand children change once a month. Every month I have to open each spreadsheet and allow 10 seconds for Bloomberg API to connect, download the relevant data and then I press print. This exercise takes around 45 minutes.

Does anyone know of a solution that allows me to print all the files at once (just the first sheet per book as per the default option when you press print on the spreadsheet) but crucially allow Bloomberg enough time to pull the data? If you print immediately after you open the spreadsheet the data is stale.

Thank you so much

**Greg M** · 02-03-2020, 09:54 PM

Hi there,

Do all of the workbook files have the same extension (e.g. "xls"), or do the various folders contain a mixture of file extensions?

Regards,

Greg M

**goodquestionmma** · 02-05-2020, 11:00 AM

They are all .xlsx

Thanks

**goodquestionmma** · 02-11-2020, 01:29 PM

@Greg M just wondering if you had any solutions you can think of?

Many thanks.

**Greg M** · 02-12-2020, 01:14 PM

Hi again,

Sorry for the delay in replying - I'm on holidays (and therefore away from Excel ) at the moment.

I think I may have a solution which meets your requirements. In fact, this is the sort of "problem" (i.e. excessive User time) which I really enjoy solving.

If you can wait for another 12 days I will be home again and able to post a possible solution here.

Regards,

Greg M

**goodquestionmma** · 02-16-2020, 04:41 PM

@Greg M

Dear Greg,

No of course not - thank you, I hope you are having a lovely holiday. Looking forward to hearing back from you!

Many thanks

**goodquestionmma** · 02-21-2020, 11:30 AM

Dear @Greg M,

I wonder if the following resource might help inform your answer?

https://stackoverflow.com/questions/...88630#22088630

Thank you

**Greg M** · 02-22-2020, 05:50 PM

Hi again,

Many thanks for your suggestion, which was very interesting. I have suggested an alternative approach which is easier for me to test, as I don't have access to your workbooks and don't know what sort of connections may be involved.

Take a look at the attached workbook and see if it does what you need. It uses the following code:

Please Login or Register  to view this content.

The highlighted values should be altered to suit your own requirements.

The process is broken down into several steps as follows:

a) create an array which contains the FullNames of each of the workbooks to be processed;

b) copy the values contained in that array to a column in the main (i.e. the attached) workbook;

c) read the first FullName in the above range and then open the Excel workbook with that name;

d) use the OnTime method to schedule the execution of a routine which prints the open workbook, closes the open workbook, reads the next FullName, and then repeats step c above;

e) exit and display a closing message when all of the FullNames have been processed.

Hope this helps - please let me know how you get on.

Regards,

Greg M

**Greg M** · 02-22-2020, 05:55 PM

Hi again,

Sorry, just realised that I was using PrintPreview for testing purposes.

You should make the highlighted change to have the output directed to the printer:

Please Login or Register  to view this content.

Regards,

Greg M

**goodquestionmma** · 02-24-2020, 12:18 PM

Dear @Greg M,

Thank you so much for this.

I am getting the following error message:

Run-time error '13';
Type mismatch

That refers to line:
dteSecondsToWait = TimeValue("00:00:" & CStr(miSECONDS_TO_WAIT))

I have set miSECONDS_TO_WAIT to 5000

Please will you help me solve this bug?

Many thanks

**goodquestionmma** · 02-24-2020, 12:28 PM

Dear @GregM,

I misread siSeconds,

I realise that the value refers to seconds and not milliseconds.

The script works perfectly.

You have read my request, considered it and produced an incredibly useful resource for me.

Thank you. I am very grateful.

**Greg M** · 02-24-2020, 03:02 PM

Hi again,

Many thanks for your feedback.

You're welcome - glad I was able to save you 45 minutes each time you need to do this!

Regards,

Greg M

How to print multiple workbooks in batch but allow for time for Bloomberg to pull data?

LinkBack

Thread Tools

Rate This Thread

Display

How to print multiple workbooks in batch but allow for time for Bloomberg to pull data?

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Re: How to print multiple workbooks in batch but allow for time for Bloomberg to pull data

Thread Information

Users Browsing this Thread

Similar Threads

Macro to Pull Data from Multiple Workbooks to One Master Workbooks

Batch open and print password protected Excel workbooks

Pull Data from Multiple Workbooks

Excel VBA to pull and process data from Bloomberg

Pull Data from Multiple Workbooks

Batch Print workbooks.

[SOLVED] Need2 batch print all the workbooks in 25 worksheets in one step

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions