Import data from a closed workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all-

I have an opened workbook that needs to pull data from a closed workbook.
Does anyone have a macro that can pull data from the closed workbook and
import it into the opened workbook? I don't know how many rows of data
there are, but there are always 4 columns. I am currently using the code
below, but it only pulls 100 rows of data. I need a macro that will pull
tdata from the last used row. Can any tweak the code or maybe have a better
one? Thanks

Sub Retrieve_Info()
P = "C:\Documents and Settings\David Truong\Desktop"
f = "Book2.xlsm"
s = "Sheet1"

Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 3
a = Cells(r, c).Address
Cells(r, c) = GetValue(P, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub


Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String


' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a2").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
 
Hi David:

If you are currently pulling the first 100 rows of data and want to pull ALL
the data in the columns, why not just pull in excess??

In place of:
For r = 1 To 100
use:
For r=1 to 10000

You will be pulling lots of un-needed empty cells, but so what. This way
you avoid having to know .UsedRange in the closed workbook.




An alternative is to store the row number of the last used row in the closed
workbook itself. So if Z100 in the closed workbook contains the number of
used rows, your code could first pull Z100 and then use the result ain the
For for the rest of the pulls.
 
Hey Gary's Student,

I originally tried that but it took too long for the macro to run and in
addition to that, it freezes up my computer.

Gary''s Student said:
Hi David:

If you are currently pulling the first 100 rows of data and want to pull ALL
the data in the columns, why not just pull in excess??

In place of:
For r = 1 To 100
use:
For r=1 to 10000

You will be pulling lots of un-needed empty cells, but so what. This way
you avoid having to know .UsedRange in the closed workbook.




An alternative is to store the row number of the last used row in the closed
workbook itself. So if Z100 in the closed workbook contains the number of
used rows, your code could first pull Z100 and then use the result ain the
For for the rest of the pulls.
--
Gary''s Student - gsnu200744


David T said:
Hello all-

I have an opened workbook that needs to pull data from a closed workbook.
Does anyone have a macro that can pull data from the closed workbook and
import it into the opened workbook? I don't know how many rows of data
there are, but there are always 4 columns. I am currently using the code
below, but it only pulls 100 rows of data. I need a macro that will pull
tdata from the last used row. Can any tweak the code or maybe have a better
one? Thanks

Sub Retrieve_Info()
P = "C:\Documents and Settings\David Truong\Desktop"
f = "Book2.xlsm"
s = "Sheet1"

Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 3
a = Cells(r, c).Address
Cells(r, c) = GetValue(P, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub


Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String


' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a2").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Click to expand...
Click to expand...
 
Back
Top