Import data from excel files with specific file name into another excel sheet

[Nicolina]

Hi, I need a function/Macro that allows me to import data from files with a specific file name.

More background:
I am downloading a large amount of excel files from an webpage containing Agricultural data for different countries (all files have the same structure in terms of rows and columns). This is done with an Python code that names the files according to a standard of: Country + Crop + Irrigation level + Management level. For instance: GhanaMaizeIrrigationLow.

I have created a excel with drop down menus to select each of these parameters and through the TEXTJOIN function merges it in accordance with the naming of the files.I now need some function/Macro that will import data from specific cells in the file that has the corresponding name. Is there a not too complicated way of doing this?

Thank you so much in advance!

[offthelip]

You will need to change this to get exactly what you want, but this should give you the idea,

Sub openfiles()Path = ActiveWorkbook.Path
Aname = ActiveWorkbook.Name
nn = 2   
filenm = Cells(3, 2)  
Workbooks.Open Filename:=filenm
With Worksheets("sheet2")
   inarr = Range(.Cells(1, 1), .Cells(1 + nn, 2))
End With
 Workbooks(Aname).Activate
With Worksheets("Sheet1")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  Range(.Cells(lastrow + 1, 1), .Cells(lastrow + 1 + nn, 2)) = inarr
End With
End Sub

I assume the filename is in B2 of the active sheet, it picks up cells from A1 to B(1+nn) and puts the date into next vacant row on sheet one of the original workbook

[Nicolina]

You will need to change this to get exactly what you want, but this should give you the idea,

Sub openfiles()Path = ActiveWorkbook.Path
Aname = ActiveWorkbook.Name
nn = 2   
filenm = Cells(3, 2)  
Workbooks.Open Filename:=filenm
With Worksheets("sheet2")
   inarr = Range(.Cells(1, 1), .Cells(1 + nn, 2))
End With
 Workbooks(Aname).Activate
With Worksheets("Sheet1")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  Range(.Cells(lastrow + 1, 1), .Cells(lastrow + 1 + nn, 2)) = inarr
End With
End Sub

I assume the filename is in B2 of the active sheet, it picks up cells from A1 to B(1+nn) and puts the date into next vacant row on sheet one of the original workbook

Thank you for your reply, as I understand this requires me to have the export file opened? Since there will be so many files this is something that I am trying to avoid. But as you said it might need some work, so it will most likely be helpful for me, so thanks again!

[offthelip]

Why not download the data from the web pages straight into Excel if you want it in EXCEL?

[Nicolina]

Thank you. That is one good point, but this is they way my supervisor has asked me to do..

I have got this code to run (below), however it requires me to state the file name in the code ( "Test" in the line: "fCSV = Dir(ThisWorkbook.Path & "\Test.csv") 'start the CSV file listing" ). I want to import the file name from from cell "B2" in Sheet "Input" and the same workbook. This file name in that cell is created through the TEXTJOIN function. Would you happen to have any input on this or know how I can refer to this cell. I have tried this:

fPath = ThisWorkbook.Sheets("Input").Range("B8").Value
fCSV = Dir(fPath & "*.csv")

The code runs without errors but it doesn't import any data. So I assume it is not able to recognize it as an file name.

I really appreciate any comments or feedback! Thanks!


Option Explicit


Sub ImportCSVsWithReference()




Dim wbCSV As Workbook
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("Import_all")
Dim fPath As String: fPath = "C:\Users\nicol\Desktop\GIS\GAEZ_Downloads\CSV_Files\" 'path to CSV files, include the final \
Dim fCSV As String






If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
= vbYes Then wsMstr.UsedRange.Clear


Application.ScreenUpdating = True




fCSV = Dir(ThisWorkbook.Path & "\Test.csv") 'start the CSV file listing




Do While Len(fCSV) > 0
'open a CSV file
Set wbCSV = Workbooks.Open(fPath & fCSV)
'insert col A and add CSV name
Columns(1).Insert xlShiftToRight
Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
'copy date into master sheet and close source file
ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbCSV.Close False
'ready next CSV
fCSV = Dir
Loop




Application.ScreenUpdating = True
End Sub

[offthelip]

your requirements are not clear, your first post states:

I now need some function/Macro that will import data from specific cells in the file that has the corresponding name.

This appears to ask to open one file and grab some cells and copy them to master file, I believe my response #2 does exactly this.

Your last post seems to open every csv file in folder and concatenate the data.

what are you trying to do?

1: How do you specify which files you want to get the data from. Are they all in the same directory?
2: Do you want to open lots of files in a loop of some sort, or just one at time? If many files how does the "join" text relate to the files you want to open?
3: what cells do you want copied from the source files?
4: where do you want the source data to go in the master file.

[Nicolina]

I know that it my code is a bit ad-hook, since I based it one one that imported data from multiple sources, however I only want to import from one at a time.

After your comment I have cleaned it up a bit. Please see:

Sub ImportCSVsWithReference()


Dim wbCSV As Workbook
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("Import_all")
Dim fPath As String: fPath = "C:\Users\nicol\Desktop\GIS\GAEZ_Downloads\CSV_Files\" 'path to CSV files, include the final \
Dim fCSV As String


If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
= vbYes Then wsMstr.UsedRange.Clear


Application.ScreenUpdating = True


fCSV = Dir(ThisWorkbook.Path & "\TEST.csv")


Set wbCSV = Workbooks.Open(fPath & fCSV)


'insert col A and add CSV name
Columns(1).Insert xlShiftToRight
Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name


'copy date into master sheet and close source file
ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbCSV.Close False




Application.ScreenUpdating = True
End Sub

To answer your questions:

1. All the files are located in the same folder. It will always only be one file to import from. Which file that is, depends on what the user has specified earlier in the master excel.
2. It is not longer part of the code.
3. I want all the content in the file to be imported. (This works )
4. I want it to be imported to a another sheet called "Import_all" (This works )

Let me know if there is any other uncertainty! Thank you!

[offthelip]

I am not clear what you are trying to do but the following code will open a file as specified by a name in B3 and copies the used range on sheet2 to the next blank row in Sheet1 of this workbook
Change "Path" to the path you want and change "sheet1" and "sheet2" to the worksheet names that you want.
I think your code doesn't work because you are not taking care of which workbook you are in, but that is probably not the only problem. Note in my code I save the workbook name so that I can swap back to it after reading the data from the file that is opened.

Sub openfiles()
Path = ActiveWorkbook.Path
Aname = ActiveWorkbook.Name  ' save the workbook name for later


filenm = Cells(3, 2)
Workbooks.Open Filename:=Path & "\" & filenm ' this will mean the active  workbook is the changed to the newly opened workbook
With Worksheets("sheet2")
 lastcolumn = .UsedRange.Columns(.UsedRange.Columns.Count).Column
 lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
   inarr = Range(.Cells(1, 1), .Cells(lastrow, lastcolumn))
End With
 Workbooks(Aname).Activate ' swap back to the original workbook 
With Worksheets("Sheet1")
  alastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  Range(.Cells(alastrow + 1, 1), .Cells(alastrow + 1 + lastrow, lastcolumn)) = inarr
End With
End Sub

[Nicolina]

Thank you for all the help! This Code does the job!

Sub openfiles2()


Dim wbCSV As Workbook
Dim wsMstr As Worksheet: Set wsMstr = ThisWorkbook.Sheets("WORKBOOKNAME")
Dim fPath As String: fPath = "C:\" 'path to CSV files, include the final \
Dim fCSV As String


Path = ActiveWorkbook.Path
Aname = ActiveWorkbook.Name ' save the workbook name for later


If MsgBox("Clear the existing data in sheet Import_all_Suitability before importing?", vbYesNo, "Clear?") _
= vbYes Then wsMstr.UsedRange.Clear


Application.ScreenUpdating = False 'speed up macro


filenm = Cells(7, 8)
Workbooks.Open Filename:=Path & "\" & filenm ' this will mean the active workbook is the changed to the newly opened workbook
Bname = ActiveWorkbook.Name ' save the workbook name for later



Set wbCSV = Workbooks.Open(Path & "\" & filenm)

ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
wbCSV.Close False


End Sub