Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
workbook open in looping
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" wrote in message ... 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 ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
workbook open in looping
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..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping through list of workbooks to open & sheets to move | Excel Programming | |||
Looping thru open word documents to find a specific one | Excel Programming | |||
Open a specific workbook...find value from other open workbook and then insert cells values in cell next to it. | Excel Programming | |||
Looping a column in workbook 1 to workbook 2's sheets | Excel Programming | |||
VBA code for looping through open workbooks and worksheets | Excel Programming |