Import Data from Closed Workbook

snigacookie

New Member
Joined
Apr 15, 2008
Messages
24
Hi all,

I have hit a wall as to create an open event to import data from another workbook. This process has to be automated.

Any ideas on where to start?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
something like workbooks.open "C:\YOURPATH\YOURFILENAME.xls" maybe? If you tell us what you want to achieve and how much you know about macros / vba the easier it would be to help.
 
Upvote 0
it's not possible to pull in data from a closed Workbook (without opening it). The code below is a workaround.

It places an IF formula in the same range as the UsedRange of the closed Workbook (Book1.xls) within the open Workbook (Book2.xls) and pulls in the data from the closed Workbook (Book1.xls). If the cell it is referencing is blank, it puts an #N/A in it's place. I then use the SpecialCells Method to delete all #N/A errors. Last of all it changes all formulas to Values only.

You first put some simple code in Book1.xls (the closed Workbook) that will give you the exact area Address of the UsedRange on Sheet1. You must place this in the Private Module of ThisWorkbook i.e Workbook_BeforeSave.


Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean,Cancel As Boolean)
'Put in the UsedRange Address of Sheet1 Book1.xls (this workbook)
Sheet2.Cells(1, 1) = Sheet1.UsedRange.Address
End Sub
--------------------------------------------------------------------------------
Now in the Workbook you want to pull the data in (Book2.xls), place this code in a Standard Module.
--------------------------------------------------------------------------------

Sub PullInSheet1()
'Pulls in all data from sheet1 of a closed workbook.

Dim AreaAddress As String

'Clear sheet ready for new data
Sheet1.UsedRange.Clear
'Reference the UsedRange Address of Sheet1 _
in the closed Workbook.
Sheet1.Cells(1, 1) = "= 'C:\My Documents\myfolder\" _
& "[Book1.xls]Sheet2'!RC"
'Pass the area Address to a String
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Range(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not _
empty the pull in it's content, else put in an Error.
.FormulaR1C1 = "=IF('C:\My Documents\myfolder\" _
& "[Book1.xls]Sheet1'!RC="""",NA(),'C:\My Documents\" _
& "myfolder\[Book1.xls]Sheet1'!RC)"
'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only
.Value = .Value
End With
End Sub
 
Upvote 0
This is what I currently have. Everything work but I do know know why i get an overflow error @ 'y=y+1'.


Code:
Sub Import_Removal_Data()
Dim wb As Workbook
Dim path As String
Dim x, y As Integer
Dim temp1, temp2 As Variant
 
Application.ScreenUpdating = False
path = "D:\Documents and Settings\xiae1703\Desktop\Project\"
Set wb = Workbooks.Open(path & "Fleet Hours and Cycles.xls", True, True)
'x is the source row
'y is the main data sheet row where it will be inputed
x = 2
y = 1
With thisworkbook.Worksheets(1)

temp1 = Worksheets(1).Cells(x, 1).Value
temp2 = Worksheets(1).Cells(x, 4).Value
 
c = 0
While c < 5

If temp1 <> "" Then
'Sets cells in main workbook equal to cells in other data workbook
thisworkbook.Worksheets(1).Cells(y, 1) = wb.Worksheets(1).Cells(x, 1)
thisworkbook.Worksheets(1).Cells(y, 2) = wb.Worksheets(1).Cells(x, 2)
y = y + 1
End If
x = x + 1
If temp2 = "" Then
    c = c + 1
Else
    c = 0
End If
If c > 5 Then wb.Worksheets(1).Activate

Wend
End With
wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
 
End Sub
 
Upvote 0
Try this.
Code:
Dim x As Long 
Dimy As Long
 
Upvote 0
That's nothing to do with the change I suggested.

It must be erroring now for some other reason.

Which line is highlightes when it happens?
 
Upvote 0
Well the only thing wrong with what I posted is a slight typo, and I thought you would pick that up.

But that would cause a syntax error not a runtime error which is what you are getting now.
Code:
Dim x As Long
Dim y As Long
 
Upvote 0
Hi,

To get data from a closed workbook, see the following link.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

A long time ago Nimrod suggested this trick:
Code:
Sub test() 
GetValuesFromAClosedWorkbook "C:\folder\anotherfolder", "Book1.xls","Sheet1", "A1" 
End Sub
 
Sub GetValuesFromAClosedWorkbook(fPath As String, _ 
fName As String, sName, cellRange As String) 
With ActiveSheet.Range(cellRange) 
.FormulaArray = "='" & fPath & "\[" & fName & "]" _ 
& sName & "'!" & cellRange 
.Value = .Value 
End With
End Sub
(didn't test this)
kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top