These are some steps to accomplish what you want here...
- Identify the spreadsheets
From what I saw in your example, the name varies not only according to the date, but also the time, because in one of the cited files you placed '0900' = '9:00h' and another with '1000' = '10:00'.
1.1 If you have a pattern that changes only the date, it is easier, because it would generate in the file 'manage.xls' a routine to create the name of the files in order to get a specific date, the day or the day before, if applicable.
' Para pegar o arquivo de hoje
="matriz_"&TEXTO(AGORA();"ddmmaaaa")&"1000"
Adapt the above example to find the desired date at the desired time, if a fixed time.
1.2 If you don’t have this fixed file name, you can create a specific folder that will only have the file you want to read and do a routine (function) to read all the files in this folder according to this template I found:
Lists all files in a folder
This code is quite complete and can be adapted to search from a fixed and fixed.xls folder as needed.
After knowing the file that has to be referenced, you can fetch the data!
- Data search
2.1 The search for the data can be direct as shown in your example, if it works as you wish, ok!
2.2 If you want to copy this data, you can have a temporary 'tab' to copy the data, work and write to the desired table. Depending on the complexity of the search, if you have filters to be made etc, I use a direct connection via ODBC (Excel Files) to filter using SQL the data I want and the necessary filters. In some project I have made used this function below that can be adapted according to your demand:
Sub SQLPesq(ByRef WHAREHOUSE As String, ByRef PATHFILE As String, ByRef PATH As String)
' Macro para pesquisar no arquivo selecionado, dados do armazém selecionado
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=" & PATHFILE & ";DefaultDir" _
), Array( _
"=" & PATH & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range(WHAREHOUSE)).QueryTable
.CommandText = Array( _
"SELECT `RESUMO$`.Clientes, `RESUMO$`.`Descrição do Armazém`, `RESUMO$`.IE, `RESUMO$`.`Tipo de Grão`, `RESUMO$`.`Total Geral`" & Chr(13) & "" & Chr(10) & _
"FROM `RESUMO$` `RESUMO$`" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "tbl" & WHAREHOUSE
.Refresh BackgroundQuery:=False
End With
End Sub
After this search would have the way to work the data, manipulate, format and write this information in the table, but I think it would be outside the scope of what you asked.
I hope I’ve helped!
From what I understand, How to get information from another spreadsheet you already know how to do. However I would like to do it dynamically and not manually?
– Franchesco
Yes, exactly. However, I need to add the values of the spreadsheet (dynamically) matriz_070120150900.xls with the worksheet of the next day matriz_080120151000.xls, which will be generated from 08/01 to 10:00
– Andre Oliveira
The system generates spreadsheets with the same columns. I need the worksheet generating.xls to identify the worksheets with the name that starts with "matriz_" and add the values of column "B1", for example.
– Andre Oliveira
It is not the solution but a great help for your question, look at this link http://www.vbaexpress.com/kb/getarticle.php?kb_id=1042
– jsantos1991
the spreadsheet
gerencia.xlstake the information of the previous spreadsheets or only the one of the current day? All spreadsheets, including thegerencia.xls, are in the same folder?– mateusalxd