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

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
49
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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
 

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
49
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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,485
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-questions/1086070-macros-editing-multiple-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
 

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
49
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.
 

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
49
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-questions/1086070-macros-editing-multiple-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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,839
Messages
5,489,177
Members
407,675
Latest member
meaghutter

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top