Results 1 to 7 of 7

Thread: How to run VBA code in multiple workbooks from one workbook.

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to run VBA code in multiple workbooks from one workbook.

    I have VBA code which retrieves historical stock prices data for the last number of periods (days, weeks or months) up to the endDate specified by the user in the workbook.
    If I try to download data for more than 100 shares at a time, the speed of download progressively decreases.
    The only solution I found is to create several Workbooks with different lists, 100 shares in each one, all of them eventually export (part of downloaded information selected by certain algorithm) to file DataFile.
    The inconvenience is that if I want to download price data on a daily basis, I have to manually open each file, change endDate, download data up to this date, close file then open the next one etc.
    I’m trying to write macros which would allow me to set endDate in DataFile as a reference for the rest of the workbooks and run all these files one after another by single macros.
    Sub GetSecurityHistoricalData runs via Call function:

    Call GetSecurityHistoricalData(ThisWorkbook.Worksheets("Control").Range("ControlExtractEndDate").Value, _ dblHistoricalData, dteHistoricalDate, _blnAbort, blnExtractError, blnWarningFound, strURL, _ strResponse, intErrorCount)

    When I tried to change:
    ThisWorkbook.Worksheets("Control").Range("ControlExtractEndDate").Value,
    To
    Workbooks(“DataFile.xlsm”).Worksheets(“Control”).Range("ControlExtractEndDate"). Value,

    It gives Run-Time error ‘9’
    Subscript out of range

    I could not find reference to endDate within Sub GetSecurityHistoricalData itself in order to change it to DataFile.
    Sorry if description of the problem is confusing, English is not my native language. Would be grateful for suggestions to fix problem.
    Thanks in advance.

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,645
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: How to run VBA code in multiple workbooks from one workbook.

    dilshod_k,

    See if the following link will help you.

    http://msdn.microsoft.com/en-us/library/cc837974.aspx
    Merging Data from Multiple Workbooks into a Summary Workbook in Excel
    Merge data from all workbooks in a folder
    Merging a Range from Selected Workbooks
    Merging a Range from Multiple Workbooks by Column
    Merging a Range from Multiple Workbooks in a Folder with a Filter
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run VBA code in multiple workbooks from one workbook.

    Quote Originally Posted by hiker95 View Post
    dilshod_k,

    See if the following link will help you.

    http://msdn.microsoft.com/en-us/library/cc837974.aspx
    Merging Data from Multiple Workbooks into a Summary Workbook in Excel
    Merge data from all workbooks in a folder
    Merging a Range from Selected Workbooks
    Merging a Range from Multiple Workbooks by Column
    Merging a Range from Multiple Workbooks in a Folder with a Filter
    Thanks for the link.
    Apparently I could not explain properly that it is not a problem of data merging.
    Thanks anyway.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,645
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: How to run VBA code in multiple workbooks from one workbook.

    dilshod_k,

    It is always easier to help and test possible solutions if we could work with your actual file.

    Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

    Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

    Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

    If the workbook contains confidential information, you could replace it with generic data.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  5. #5
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,277
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to run VBA code in multiple workbooks from one workbook.

    FYI hiker95...
    This has already been addressed...
    "The inconvenience is that if I want to download price data on a daily basis, I have to manually open each file, change endDate, download data up to this date, close file then open the next one etc.
    I’m trying to write macros which would allow me to set endDate in DataFile as a reference for the rest of the workbooks and run all these files one after another by single macros."
    here...
    https://www.mrexcel.com/forum/excel-...workbooks.html
    Not sure why dilshod_k is still kicking this around? Dave

    ps DataFile.xlsm is ThisWorkbook in this eg. and the ControlExtractEndDate is a named range that is supposed to exist

  6. #6
    New Member
    Join Date
    Feb 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run VBA code in multiple workbooks from one workbook.

    Quote Originally Posted by hiker95 View Post
    dilshod_k,

    It is always easier to help and test possible solutions if we could work with your actual file.

    Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

    Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

    Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

    If the workbook contains confidential information, you could replace it with generic data.

    Thanks for advice. I certainly will in the future. At the moment I succeeded to complete working version of the code.

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run VBA code in multiple workbooks from one workbook.

    Quote Originally Posted by NdNoviceHlp View Post
    FYI hiker95...
    This has already been addressed...
    "The inconvenience is that if I want to download price data on a daily basis, I have to manually open each file, change endDate, download data up to this date, close file then open the next one etc.
    I’m trying to write macros which would allow me to set endDate in DataFile as a reference for the rest of the workbooks and run all these files one after another by single macros."
    here...
    https://www.mrexcel.com/forum/excel-...workbooks.html
    Not sure why dilshod_k is still kicking this around? Dave

    ps DataFile.xlsm is ThisWorkbook in this eg. and the ControlExtractEndDate is a named range that is supposed to exist
    Hi Dave,
    Thanks for your help. With slight modifications to your code I succeeded to get what I want (for the time being). Please find the final version of the code below:

    Sub MasterKeyNdNoviceHlp()


    Dim FSO As Object, FlDr As Object, Fl As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    Set FlDr = FSO.GetFolder("C:\Users\User\Downloads\Trading\Test\Master DataFile")


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    On Error Resume Next


    For Each Fl In FlDr.Files
    If Fl.Name <> "DataFile.xlsm" Then
    Workbooks.Open Filename:=Fl

    ' I've placed EndDate into cell U1 of DataFile so that I don't have to change EndDate in each file manually right before data download.


    ActiveWorkbook.Sheets("Control").Range("B" & 14) = _
    Workbooks("DataFile.xlsm").Sheets("Control").Range("U" & 1).Value


    Dim intResponse As Integer
    Dim wsWorksheet As Worksheet
    Dim i As Integer


    Application.DisplayAlerts = True

    For Each wsWorksheet In ActiveWorkbook.Worksheets
    If wsWorksheet.Name <> "Control" And wsWorksheet.Name <> "Response" Then
    wsWorksheet.Delete
    i = i + 1
    End If
    Next


    Application.DisplayAlerts = False

    wrk = ActiveWorkbook.Name


    Application.Run ("'" & wrk & "'" & "!ExtractHistoricalData")
    Application.Run ("'" & wrk & "'" & "!A_BUY_SELL_Signals_MasterSheet")
    Application.Run ("'" & wrk & "'" & "!B_Delete_Empty_Rows")
    Application.Run ("'" & wrk & "'" & "!Export_Data")






    Workbooks(Fl.Name).Close SaveChanges:=True
    End If
    Next Fl


    Set FlDr = Nothing
    Set FSO = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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