workbook open in looping

[KK]

Private Sub cbFile_Click()

'returns your full file name.
File_Name = Application.GetOpenFilename("MS Excel Files (*.xls),*.xls")

'hence no len, no name...
If Len(File_Name) = 0 Then Exit Sub

tbFile.value = File_Name

End Sub

Private Sub cbStart_Click()

Workbooks.Open (tbFile)

Dim x1 As Integer
Dim y13 As Integer

Dim value As String
Dim count As String
Set wb = Workbooks.Open(tbFile, True, True)

y1 = 6
For x1 = 2 To 930
Worksheets("Task_Table1").Select

Application.Goto Reference:="R" & x1 & "C20"

ActiveCell.FormulaR1C1 =
"=IF((LEFT(RC[-17],LEN(RC[-17])-4)+1)>11,(IF(RC[-13]<>""Yes"",(IF(RC[-10]<>1,""YES"",""No"")),""No"")),""No"")"
value = ActiveCell.value





If (value <> "No") Then

Workbooks("Project_Analyzer_v2").Activate

' open the source workbook, read only
With ThisWorkbook.Worksheets("Duration Test")


..Range("B1").Formula = "File : " & tbFile.value
..Range("B2").Formula = "Test Completed : " & FormatDateTime(Now())

..Range("A5").Formula = "ID"
..Range("B5").Formula = "Task"
..Range("C5").Formula = "Duration"
..Range("D5").Formula = "Start Date"
..Range("E5").Formula = "Finish Date"
' read data from the source workbook
..Range("A" & y1).Formula = wb.Worksheets("Task_Table1").Range("A" &
x1).Formula
..Range("B" & y1).Formula = wb.Worksheets("Task_Table1").Range("B" &
x1).Formula
..Range("C" & y1).Formula = wb.Worksheets("Task_Table1").Range("C" &
x1).Formula
..Range("D" & y1).Formula = wb.Worksheets("Task_Table1").Range("D" &
x1).Formula
..Range("E" & y1).Formula = wb.Worksheets("Task_Table1").Range("E" &
x1).Formula

End With
y1 = y1 + 1

Else


End If
Next x1

End Sub


This is my coding,I had migrate out the set wb out ,but it run once
then pop out run time error "9" subscript out of range, if a add the
windows("tbFile").activate before the worksheet("Task_Table1") , same the
error , add windows(tbFile).activate then the error "type mismatch", i also
try to change windows(tbFile) to workbooks(tbFile) ,the error "type mismatch"
is display,anyone can help me ?

 

[Patrick Molloy]

instead of
tbFile.value = File_Name

you could put
cbStart_Click File_Name
adding the name as a string parameter

you don't say what tbFile is, so I'd guess its a range object( ie a cell)
so
windows("tbFile").
doesn't make sense

and your
Workbooks.Open (tbFile)
maybe should be
(if file_name IS NOT passed you need
File_Name = tbFile.Value
)

Workbooks.Open (File_Name)

 

[KK]

Private Sub cbFile_Click()

'returns your full file name.
File_Name = Application.GetOpenFilename("MS Excel Files (*.xls),*.xls")

'hence no len, no name...
If Len(File_Name) = 0 Then Exit Sub

tbFile.value = File_Name

End Sub

Private Sub cbStart_Click()

Workbooks.Open (tbFile)

Dim x1 As Integer
Dim y1 As Integer

Dim value As String
Dim Name As String
Name = ActiveWorkbook.Name


y1 = 6
For x1 = 2 To 930
Workbooks(Name).Activate
Sheets("Task_Table1").Select
Application.Goto Reference:="R" & x1 & "C20"

ActiveCell.FormulaR1C1 =
"=IF((LEFT(RC[-17],LEN(RC[-17])-4)+1)>11,(IF(RC[-13]<>""Yes"",(IF(RC[-10]<>1,""YES"",""No"")),""No"")),""No"")"
value = ActiveCell.value





If (value <> "No") Then
Workbooks("Project_Analyzer_v2").Activate
ActiveSheet.Cells.Select
ActiveSheet.UsedRange.Columns.AutoFit



Application.ScreenUpdating = False ' turn off the screen updating

' open the source workbook, read only
With ThisWorkbook.Worksheets("Duration Test")

.Range("B1").Formula = "File : " & tbFile.value
.Range("B2").Formula = "Test Completed : " & FormatDateTime(Now())

.Range("A5").Formula = "ID"
.Range("B5").Formula = "Task"
.Range("C5").Formula = "Duration"
.Range("D5").Formula = "Start Date"
.Range("E5").Formula = "Finish Date"
' read data from the source workbook
.Range("A" & y1).Formula =
Workbooks(Name).Worksheets("Task_Table1").Range("A" & x1).Formula
.Range("B" & y1).Formula =
Workbooks(Name).Worksheets("Task_Table1").Range("B" & x1).Formula
.Range("C" & y1).Formula =
Workbooks(Name).Worksheets("Task_Table1").Range("C" & x1).Formula
.Range("D" & y1).Formula =
Workbooks(Name).Worksheets("Task_Table1").Range("D" & x1).Formula
.Range("E" & y1).Formula =
Workbooks(Name).Worksheets("Task_Table1").Range("E" & x1).Formula

End With

Application.ScreenUpdating = True ' turn on the screen updating
y1 = y1 + 1


Else
End If
Next x1

If (y1 = 6) Then

Workbooks("Project_Analyzer_v2").Activate


Application.ScreenUpdating = False ' turn off the screen updating


With ThisWorkbook.Worksheets("Duration Test")

.Range("B1").Formula = "File : " & tbFile.value
.Range("B2").Formula = "Test Completed : " & FormatDateTime(Now())

.Range("A5").Formula = "ID"
.Range("B5").Formula = "Task"
.Range("C5").Formula = "Duration"
.Range("D5").Formula = "Start Date"
.Range("E5").Formula = "Finish Date"
.Range("F5").Formula = "Predecessor"
.Range("G5").Formula = "Successor"

.Range("B6").Formula = "NO TASKS TO REPORT "

End With
End If

End Sub


I had solve the problem , the problem is i saved whole path of file name in
tbFile , so i cant use the Workbooks() function , so i get the path of the
workbooks name only .....then it work ......thanks to Patrick and Sam.....