MS Excel Macro:Data Import, Dynamic file name?

Hello



How do I create a macro to import data from another excel file?

Column A is all I need, for about 50 rows.



The thing is, the input file's name is changed daily.



So Monday, it's ABC201008

Tuesday, ABC 211008

etc



Thanks



MS Excel Macro:Data Import, Dynamic file name?First you are going to need to obtain the file name. If you don檛 have another way, the first part of the code below indicates a possible way. The remaining code shows a way to retrieve the data you want.



If

1. The file is always in the same directory (used C:\xls in the example),

2. The name has a recognizable form compared to other files in the directory (used ABC*.xls in the example),

3. The most recent file would always be last in an ascending sort.

4. You want to copy the values in Column A, Rows 5 to 55 of the second file to the same place in the first file.



Then the following is an example of code that will work



Sub GetData()

Dim sFileName, sNextName As String, iRow As Long

sFileName = %26quot;%26quot;

sNextName = Dir$(%26quot;C:\xls\ABC*.xls%26quot;)



Do While Len(sNextName) %26gt; 0

If sFileName %26lt; sNextName Then sFileName = sNextName

sNextName = Dir$

Loop



If sFileName = %26quot;%26quot; Then Exit Sub



Workbooks.Open Filename:=%26quot;C:\xls\%26quot; + _

sFileName, ReadOnly:=%26quot;True%26quot;



For iRow = 5 To 55

ThisWorkbook.Sheets(1).Cells(iRow, 1) = _

Workbooks(Workbooks.Count).Sheets(1).C?1)

Next



Workbooks(Workbooks.Count).Close



End Sub



With luck you will only need to change the Path in the first Dir$ and the Cell references in the For ?Next loop to have a working subroutine, otherwise it should not be difficult to figure out any other adjustments you need



You may want to add some error checking and reporting (e.g. a message box before the Exit Sub)





MS Excel Macro:Data Import, Dynamic file name?yrvw! Glad to help. thanks for the feedback.

Report Abuse