fabio.geraci
08-16-2010, 03:18 AM
Morning Everyone,
I amde the following macro, as you can see the only thing it does is to open and close a few files, in order to update (populate) a worksheet. But as soon as the macro closes the file all the data get deleted (#REF).
How can I change this macro in order to avoid this secondary effect? :doh:
Thanks
Sub update_botton()
'
' update_botton Macro
' Macro recorded 16/08/2010 by fgeraci
'
Dim Filter As String
Dim Title As Variant
Dim MyPath As String
Dim DestWb As Workbook
Dim DestWs As Worksheet
Dim Tempbook As Workbook
Dim sText As Variant
Dim i As Integer
Dim icount As Integer
'
Set DestWs = ActiveSheet
icount = Range("k2")
' Cells(11, 2)
' Set DestWb = Workbooks(InputBox("INSERT LoadCase Name:", "FileName", "RF_CUTOUT_.xls"))
MyPath = Range("b1")
For i = 1 To icount
Title = Cells(i + 1, 8)
sText = MyPath + "\" + Title
Workbooks.Open Filename:=sText
ActiveWindow.Close
Next i
End Sub
I amde the following macro, as you can see the only thing it does is to open and close a few files, in order to update (populate) a worksheet. But as soon as the macro closes the file all the data get deleted (#REF).
How can I change this macro in order to avoid this secondary effect? :doh:
Thanks
Sub update_botton()
'
' update_botton Macro
' Macro recorded 16/08/2010 by fgeraci
'
Dim Filter As String
Dim Title As Variant
Dim MyPath As String
Dim DestWb As Workbook
Dim DestWs As Worksheet
Dim Tempbook As Workbook
Dim sText As Variant
Dim i As Integer
Dim icount As Integer
'
Set DestWs = ActiveSheet
icount = Range("k2")
' Cells(11, 2)
' Set DestWb = Workbooks(InputBox("INSERT LoadCase Name:", "FileName", "RF_CUTOUT_.xls"))
MyPath = Range("b1")
For i = 1 To icount
Title = Cells(i + 1, 8)
sText = MyPath + "\" + Title
Workbooks.Open Filename:=sText
ActiveWindow.Close
Next i
End Sub