Pull cell values from closed workbook in same folder into another file opened from same folder

[syndee]

I have 4 files in 1 folder. File1, 2, & 3.
The 4th file is where I need the data from other workbooks.
So, I need data from File 2 from Range A2 to I5000 to populate Range a2 to 5000 in 4th File if cells are not blank.

I need this to happen automatically when I open the 4th file without having to open File 2.
I also don't want cells that are blank to be retrieved.

I tried linking cells but file freezes. Tried a macro but keeps asking me to open a file.

Anyone have any ideas?

 

[geo77]

Syndee, the code below works, I tested it. When file “4” is open, the macro will automatically open file “2” copy the information [A2:C20] into a 2D array (called “buffer_array(j,i)”) and then close file “2” and paste the info in the same range in the original file (“4”). I don’t think the read can be done from a closed file.


For simplicity, I used a smaller range [A2:C20] but you can define any range. Per your request if some cells are blank in file 2, the macro will not do anything (leaving that content in file “4” unchanged in those same cells).



Carefully change the path of the files (in VBA code) to suit your paths on your computer.


Also, notice that I did this in .xls (97-2003) so you will need to change that if you use the new excel.

Another thing, in order to be triggered automatically at the open of file 4, place this macro in “ThisWorkbook” section of the VBA project explorer not in a sheet or in a module section (file “4” of course which is your destination file and “2” is your source).

Please do me a favor and visit my blog linked below.

Cheers,
George


Dim FSO 'Declare Variables
Dim sFile As String
Dim dFile As String
Dim buffer_array(0 To 18, 0 To 2) As Variant
Dim i As Long
Dim j As Long
Dim Wkb As Workbook


Sub Workbook_Open()


sFile = "C:\Users\xxx\Pictures\MrExcel\Copy_range_from_file\2.xls" 'Source file name
dFile = "C:\Users\xxx\Pictures\MrExcel\Copy_range_from_file\4.xls" 'Destination file name


Set FSO = CreateObject("Scripting.FileSystemObject") 'Create Object


If FSO.FileExists(sFile) = False Then 'Checking If File Is Located in the Source Folder

MsgBox "Specified File Not Found", vbInformation, "Not Found"

Else


Set Wkb = Workbooks.Open(sFile)

For j = 0 To 2
For i = 0 To 18
If Not (Wkb.Worksheets("Sheet1").[A2].Offset(i, j) = "") Then
buffer_array(i, j) = Wkb.Worksheets("Sheet1").[A2].Offset(i, j).Value
Else
buffer_array(i, j) = "Blank"
End If
Next i
Next j


End If


Wkb.Close


For j = 0 To 2
For i = 0 To 18
If buffer_array(i, j) = "Blank" Then
Else
[A2].Offset(i, j) = buffer_array(i, j)
End If
Next i
Next j


End Sub

www.excelunusual.com - where adventure begins

 

[syndee]

Thanks. I guess I explained that poorly.
My Control File contains several worksheets.
I think I can do it with links to data.
Just having problem with updating links with auto updating and returning to Control File.xlsm
Tried this but need files to close except Control File.xlsm after updating.
Is that possible?

Private Sub Workbook_Open()


Application.ScreenUpdating = False


ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways
Workbooks.Open Filename:= _
"C:\Users\Cindy\Desktop\Excel-Trial File to eliminate pasting\File 2.csv"
Workbooks.Open Filename:= _
"C:\Users\Cindy\Desktop\Excel-Trial File to eliminate pasting\File 1.csv"


Application.ScreenUpdating = True

End Sub

 

[geo77]

Let's reset. Forget what it was said here so far.
Please state simply what you need. Plain English.
Don't give code, don't add any kind of complications any kind of names or extensions.
Clear questions are answered here in minutes.