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:
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: