PDA

View Full Version : Solved: Use data in closed workbook



Gunilla
10-18-2011, 01:35 AM
Hi
Is there a way where I can get data from a closed Workbook without open it?
Problem: I have a workbook with about 6000 addresses. (The master file) I have another workbook with 2 sheets (active workbook) where the data is changed once a month. I want to be able to look up data from the master file and copy it to one of the sheets in the active workbook.
I have temporary solved the problem by copying the masterfile into the active workbook and this is the code I have used

Sub adressS1()
'För över adresserna från Winsam till slamtömningstabellen
'Södertälje
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim i As Long, j As Long
Dim LR1 As Long
Dim LR2 As Long
Dim LR3 As Long
Application.ScreenUpdating = False
Set ws1 = ThisWorkbook.Worksheets(1)
Set ws2 = ThisWorkbook.Worksheets(2)
Set ws3 = ThisWorkbook.Worksheets(3)
LR1 = ws1.Range("B" & Rows.Count).End(xlUp).Row
LR2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
LR3 = ws3.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LR1 - 1
For j = 2 To LR2 - 1
If ws1.Cells(i, 2) = ws2.Cells(j, 2) Then
ws2.Cells(j, 24) = ws1.Cells(i, 6)
ws2.Cells(j, 25) = ws1.Cells(i, 7)
ws2.Cells(j, 26) = ws1.Cells(i, 8)
ws2.Cells(j, 27) = ws1.Cells(i, 9)
ws2.Cells(j, 28) = ws1.Cells(i, 10)
Else
End If

Next j
Next i

End Sub
It would be so much better if I could use the filename of the Master file instead of "ws1".

Do you have any suggestions?
Gunilla
PS The file is too big to enclose DS: pray2:

mancubus
10-18-2011, 06:13 AM
hi.

does this help?
http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

Paul_Hossler
10-18-2011, 06:16 AM
What I do to get data from a closed WB

Used something like this

v = GetInfoFromClosedFile("C:\Test", "Test.xlsx, "_Data", "B1")





'Ref http://www.vbaexpress.com (needed to credit the source (tnx mancubus ))
'call: WB Path on disc
' WB Name
' WS name
' Cell reference on WS in WS_Path\WB_Name
Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant

Dim arg As String

GetInfoFromClosedFile = vbNullString

If Right(wbPath, 1) <> Application.PathSeparator Then wbPath = wbPath & Application.PathSeparator

If Dir(wbPath & Application.PathSeparator & wbName) = "" Then Exit Function

arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
On Error GoTo 0

End Function


Might be issues if the WB is protected. etc.

Paul

Kenneth Hobs
10-18-2011, 01:52 PM
To get more data than a few cells, there is an R1C1 method to get all of the data on a sheet.

To get a set range, an ADO method works nicely if your data is nice. http://www.rondebruin.nl/ado.htm

Gunilla
10-18-2011, 11:32 PM
Thank you all, now I have something to work on.
Gunilla