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

[dilshod_k]

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.

 

[hiker95]

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]

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]

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]

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]

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]

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