J
Jim May
The following code works fine; except for the Userform1 I created which
is to serve as a Progress-Bar - "Macro Is Running" is content of
Textbox1- and
I have in the form code window Userform1.Initilize event Userform1.Show
vbModeless << The problem is when I Run the Macro the Userform1 comes
up but remains blank (without test) throughout.. In step-thru mode it
shows up.
I'm confused as to why it doesn't show the text while the macro is
successfully running.
Any ideas?
TIA,
Sub ExtractDataFromFiles()
Const sPath = "C:\Documents and Settings\Jim May\My Documents\"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
ActiveSheet.Range("A6:N2000").ClearContents
Load UserForm1
Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath & "*.xls")
j = 6 ' Data starts on Row 6
Do While sName <> ""
Set wb = Workbooks.Open(sPath & sName)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("J2").Value
r(2) = .Range("B4").Value
r(3) = .Range("B6").Value
r(4) = .Range("G4").Value
r(5) = .Range("G6").Value
r(6) = .Range("G6").Value
r(7) = .Range("J1").Value
r(8) = .Range("G51").Value
r(9) = .Range("G53").Value
r(10) = .Range("G54").Value
r(11) = .Range("G56").Value
r(12) = .Range("G57").Value
r(13) = .Range("G58").Value
r(14) = .Range("G59").Value
End With
wb.Close SaveChanges:=False
With ThisWorkbook.ActiveSheet
For n = 1 To 14
..Cells(j, n).Value = r(n)
Next n
End With
j = j + 1
sName = Dir
Loop
UserForm1.Hide
Unload UserForm1
End Sub
is to serve as a Progress-Bar - "Macro Is Running" is content of
Textbox1- and
I have in the form code window Userform1.Initilize event Userform1.Show
vbModeless << The problem is when I Run the Macro the Userform1 comes
up but remains blank (without test) throughout.. In step-thru mode it
shows up.
I'm confused as to why it doesn't show the text while the macro is
successfully running.
Any ideas?
TIA,
Sub ExtractDataFromFiles()
Const sPath = "C:\Documents and Settings\Jim May\My Documents\"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
ActiveSheet.Range("A6:N2000").ClearContents
Load UserForm1
Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath & "*.xls")
j = 6 ' Data starts on Row 6
Do While sName <> ""
Set wb = Workbooks.Open(sPath & sName)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("J2").Value
r(2) = .Range("B4").Value
r(3) = .Range("B6").Value
r(4) = .Range("G4").Value
r(5) = .Range("G6").Value
r(6) = .Range("G6").Value
r(7) = .Range("J1").Value
r(8) = .Range("G51").Value
r(9) = .Range("G53").Value
r(10) = .Range("G54").Value
r(11) = .Range("G56").Value
r(12) = .Range("G57").Value
r(13) = .Range("G58").Value
r(14) = .Range("G59").Value
End With
wb.Close SaveChanges:=False
With ThisWorkbook.ActiveSheet
For n = 1 To 14
..Cells(j, n).Value = r(n)
Next n
End With
j = j + 1
sName = Dir
Loop
UserForm1.Hide
Unload UserForm1
End Sub