I am after developing a macro which will copy different pieces of information from another file. My issue is that these files are arranged in monthly folders, and new ones are being added all the time. It would be easy if the data came from the same file all the time but it doesn't. Every four days or so depending on production, a new excel file is added with a different name that the data has to come from.
Can this be done? Or is it not possible because the file the information comes from isn't the same one every day?
- Moved by Winnie LiangMicrosoft contingent staff Monday, March 20, 2017 5:21 AM
Thanks for visiting our forum.
Then this forum mainly discusses general questions and feedback for Microsoft Excel, since your query is more related to macro, I'll move your question to the following MSDN forum for Excel:
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.
Please remember to mark the replies as answers if they helped.
If you have feedback for TechNet Subscriber Support, contact email@example.com.
I am after a macro which is able to look in a number of different sheets of a file to pull out information and dump it onto a summary spreadsheet, which is a separate file. Each spreadsheet has four sheets with eleven pieces of information in each sheet.
Another issue is that there are new files added a few times a week which I would also need the information taking from. These are arranged month by month in a folder for each month.
So I basically need a macro which is A) capable of looking across multiple cells on four sheets and pulling the information back into my summary sheet B) able to identify when a new file has been added and look at this too C) Look in the monthly folders where these files are stored.
Even though the information is spreadsheet out over multiple spreadsheets per month, the information required over the four sheets is always in the same cells.
Can anyone help me please?
- Merged by Celeste LiMicrosoft contingent staff, Moderator Tuesday, March 21, 2017 6:04 AM duplicated
from the description of the thread , it looks like you want to copy data from different file to your current file.
and every time there is a new file available from that you want to copy the data.
so you can try to use open file dialog in your code.
so that when you run the code , it will show you open file dialog and you can select the desire file.
so you can select any excel file , from where you can copy the data.
refer example below.
Sub demo() Dim sPath, FileSelected As String Dim sValue As String Dim wbTarget As Workbook Dim strName As String Dim myFile As Object strName = "Sheet1" Set myFile = Application.FileDialog(msoFileDialogOpen) With myFile .Title = "Choose File" .AllowMultiSelect = False .InitialFileName = "C:\Users\v-padee\Desktop\excel files" If .Show <> -1 Then Exit Sub End If FileSelected = .SelectedItems(1) End With Set wbTarget = Workbooks.Open(FileSelected) sValue = wbTarget.Sheets(1).Range("A1").Value ThisWorkbook.Sheets(1).Range("A1").Value = sValue ThisWorkbook.Save End Sub
you can change the path of initial file name and set that folder path where you store all the files.
in that folder you can sort the files, so when you open the file dialog you can see the new latest file.
so you can easily find the file.
you can modify the code as per your requirement.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Proposed as answer by Deepak Panchal10Microsoft contingent staff, Moderator Tuesday, March 21, 2017 1:32 AM
If I were you I would record a Macro to do the work. Then, look at the code and find the date part. Put that in a variable, like maybe in a cell. Something like =TODAY(), or =TODAY()+4. Finally, reference the sheet and cell (range) in your Macro. Run everything through the Task Manager, like in the example below.
To run a script when Excel opens, just follow the steps in the link below.