You're welcome. Thanks for your kind comments and the Likes
Open Multiple XLSM files from a given Folder
-
- Cross Post
-
Akhas -
June 6, 2021 at 11:14 AM -
Thread is marked as Resolved.
-
-
-
I am attaching 2 files
The first ("FileConverter") will run through all your files in the folder and do the following:
1. Remove the button
2. Remove the Data Validation
3. Save the file as .xlsx
4. Completely delete the original .xlsm file
This will just be a one-off run but should save you a ton of time rather than making those 4 changes on 400 files manually!
The second is the Controller file I mentioned. Complete the required criteria in cells D5 to D9 (none can be left empty!) then click the button.
The code will loop through all the files in a folder and update them according to the entered criteria. It will take a bit of time to update all 400 files, so you can go for a coffee break and leave it churning away! There will be a message when the code completes telling you how many files out of the total file count have been updated.
I have tested various different criteria and all works except for "Dividends" & "Splits", but I notice that fails on the Sample file you sent.
I have left your existing code much as it was, just change the indentation, and modified the variables where necessary. I also added a bit to the error message box so that it shows the file name where the error occurred.
I suggest you copy 6 or so files to a new folder and run tests on those before running the code on all 400 files.
Hi KjBox,
The code is working fine but every day when I run the Macro it says file is not found or misplaced or deleted.
Everytime , as you told I have to save the file save as and then only the the macro runs.
My worry is that every day I have to do the same.
Is there any permanent solution to this.
-
I have always found that doing the Open File > Save As (with same name & location, overwriting file already there) > Close File (without saving changes) sometimes needs to be done just once.
I will look into why you are having to do it repeatedly.
-
I have always found that doing the Open File > Save As (with same name & location, overwriting file already there) > Close File (without saving changes) sometimes needs to be done just once.
I will look into why you are having to do it repeatedly.
It happens every day but not on the same date.
-
I have always found that doing the Open File > Save As (with same name & location, overwriting file already there) > Close File (without saving changes) sometimes needs to be done just once.
I will look into why you are having to do it repeatedly.
Hi KJ,
Any solution for this?
-
-
I am attaching 2 files
The first ("FileConverter") will run through all your files in the folder and do the following:
1. Remove the button
2. Remove the Data Validation
3. Save the file as .xlsx
4. Completely delete the original .xlsm file
This will just be a one-off run but should save you a ton of time rather than making those 4 changes on 400 files manually!
The second is the Controller file I mentioned. Complete the required criteria in cells D5 to D9 (none can be left empty!) then click the button.
The code will loop through all the files in a folder and update them according to the entered criteria. It will take a bit of time to update all 400 files, so you can go for a coffee break and leave it churning away! There will be a message when the code completes telling you how many files out of the total file count have been updated.
I have tested various different criteria and all works except for "Dividends" & "Splits", but I notice that fails on the Sample file you sent.
I have left your existing code much as it was, just change the indentation, and modified the variables where necessary. I also added a bit to the error message box so that it shows the file name where the error occurred.
I suggest you copy 6 or so files to a new folder and run tests on those before running the code on all 400 files.
Hi KjBox,
Can we have progress Bar Indicator in the above file "Historical Data Controller".
-
Hi KjBox,
Can we have progress Bar Indicator in the above file "Historical Data Controller".
Any update
-
I am attaching 2 files
The first ("FileConverter") will run through all your files in the folder and do the following:
1. Remove the button
2. Remove the Data Validation
3. Save the file as .xlsx
4. Completely delete the original .xlsm file
This will just be a one-off run but should save you a ton of time rather than making those 4 changes on 400 files manually!
The second is the Controller file I mentioned. Complete the required criteria in cells D5 to D9 (none can be left empty!) then click the button.
The code will loop through all the files in a folder and update them according to the entered criteria. It will take a bit of time to update all 400 files, so you can go for a coffee break and leave it churning away! There will be a message when the code completes telling you how many files out of the total file count have been updated.
I have tested various different criteria and all works except for "Dividends" & "Splits", but I notice that fails on the Sample file you sent.
I have left your existing code much as it was, just change the indentation, and modified the variables where necessary. I also added a bit to the error message box so that it shows the file name where the error occurred.
I suggest you copy 6 or so files to a new folder and run tests on those before running the code on all 400 files.
Hi KjBox,
Sorry for disturbing you again,
The files " Historical data controller" was working fine till 30April 2022, but I think Yahoo has made some changes and now the error shows "Emulate URL: unexpexted error"
Please correct it.
-
Without knowing what changes Yahoo has made, if any, I cannot make any correction to the code.
-
I am attaching 2 files
The first ("FileConverter") will run through all your files in the folder and do the following:
1. Remove the button
2. Remove the Data Validation
3. Save the file as .xlsx
4. Completely delete the original .xlsm file
This will just be a one-off run but should save you a ton of time rather than making those 4 changes on 400 files manually!
The second is the Controller file I mentioned. Complete the required criteria in cells D5 to D9 (none can be left empty!) then click the button.
The code will loop through all the files in a folder and update them according to the entered criteria. It will take a bit of time to update all 400 files, so you can go for a coffee break and leave it churning away! There will be a message when the code completes telling you how many files out of the total file count have been updated.
I have tested various different criteria and all works except for "Dividends" & "Splits", but I notice that fails on the Sample file you sent.
I have left your existing code much as it was, just change the indentation, and modified the variables where necessary. I also added a bit to the error message box so that it shows the file name where the error occurred.
I suggest you copy 6 or so files to a new folder and run tests on those before running the code on all 400 files.
Hi KjBox,
The historicaldatacontroller.xlsm file is working correctly now.
Can you make a small change that it updates first 25 file then wait for 5 min then again updates the next 25 files and waits for 5 min and continues like this till all the files are updated.
Rest everything remains same.
Actually yahoo is not allowing to update more than 25 files at a go.
-
-
Try changing the "UpdateAllFiles" procedure to:
Code
Display MoreSub UpdateAllFiles() Dim fName, wbk As Workbook, ws As Worksheet, i&, lCnt&, sFile$ Dim dtE As Date, dtS As Date, sType$, sDtOrd$, sOut$, StatBar dtE = [EndDate]: dtS = [StartDate]: sType = [DataType] sDtOrd = [DateOrder]: sOut = [OutputType] fName = Dir("C:\Users\charl\Desktop\Akhas\") 'CHANGE this to the path to your folder ' Get current StatusBar info StatBar = Application.StatusBar 'Get count of files in the folder While (fName <> "") i = i + 1 fName = Dir Wend ' Reset fName fName = Dir("C:\Users\charl\Desktop\Akhas\") 'CHANGE this to the path to your folder Application.ScreenUpdating = 0 ii = 0: lCnt = 0 While fName <> "" lCnt = lCnt + 1 Set wbk = Workbooks.Open(fName) Set ws = wbk.Sheets("Historical") sFile = fName With ws .Activate Call HistoricalData(sType, dtS, dtE, sDtOrd, sOut, sFile) ' Enter the information used to create the table .[EndDate] = dtE: .[StartDate] = dtS: .[DataType] = sType .[DateOrder] = sDtOrd: .[OutputType] = sOut 'Save and close the workbook .Parent.Close 1 End With ' Pause for 5 minutes every 25 files If lCnt = 25 Then Application.StatusBar = ii & " of " & i & " files updated, waiting for Yahoo." Application.Wait Now + TimeValue("0:05:00") lCnt = 0 End If ' Show progress in Status Bar Application.StatusBar = ii & " of " & i & " files updated" fName = Dir Wend MsgBox ii & " Files out of " & i & " have beed updated", 64, "Historical Data Update" Application.StatusBar = StatBar End Sub
I have included a "Progress Status" message that will show in your Status Bar
-
Hi KjBox,
It is working perfectly but not showing the progress status message.
-
Just ignore the last message.
Its working perfectly well.
You are a great help and Genius.
-
You're welcome
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!