+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    03-29-2016
    Location
    London
    MS-Off Ver
    Office 2011
    Posts
    18

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

    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

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,499

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

    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

  3. #3
    Registered User
    Join Date
    03-29-2016
    Location
    London
    MS-Off Ver
    Office 2011
    Posts
    18

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

    They are all .xlsx

    Thanks

  4. #4
    Registered User
    Join Date
    03-29-2016
    Location
    London
    MS-Off Ver
    Office 2011
    Posts
    18

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

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

    Many thanks.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,499

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

    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

  6. #6
    Registered User
    Join Date
    03-29-2016
    Location
    London
    MS-Off Ver
    Office 2011
    Posts
    18

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

    @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

  7. #7
    Registered User
    Join Date
    03-29-2016
    Location
    London
    MS-Off Ver
    Office 2011
    Posts
    18

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

    Dear @Greg M,

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

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

    Thank you

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,499

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

    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
    Attached Files Attached Files
    Last edited by Greg M; 02-22-2020 at 06:31 PM. Reason: Typo corrected

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,499

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

    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

  10. #10
    Registered User
    Join Date
    03-29-2016
    Location
    London
    MS-Off Ver
    Office 2011
    Posts
    18

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

    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

  11. #11
    Registered User
    Join Date
    03-29-2016
    Location
    London
    MS-Off Ver
    Office 2011
    Posts
    18

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

    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.

  12. #12
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,499

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Pull Data from Multiple Workbooks to One Master Workbooks
    By eranajoy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-23-2019, 09:24 AM
  2. Batch open and print password protected Excel workbooks
    By mark1955 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-02-2017, 11:34 PM
  3. Pull Data from Multiple Workbooks
    By brutin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2015, 11:23 PM
  4. Excel VBA to pull and process data from Bloomberg
    By medusa1810 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2014, 09:36 PM
  5. Pull Data from Multiple Workbooks
    By prashantpradhan in forum Excel General
    Replies: 5
    Last Post: 06-14-2012, 11:04 AM
  6. Batch Print workbooks.
    By JapanDave in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-21-2011, 05:13 AM
  7. Replies: 2
    Last Post: 06-26-2006, 10:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1