| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
re : Help Need on my codes
Hi Experts,
I have a macro written below, it is able to find the file name "...H73FJ...", then open the file and continue and then able to close the file name that is being opened. But I have a problem which is .... I may have 2 types of file name in the database, eg... " ...H73FJ..." and "....H72FJ...." So I would like my macros to do this.... to find file name "...H72FJ..." if found, open the workbook and run application "V3_NewDeltaVH_Sort" and then close at the end, if not found, continue to find file name "....H73FJ....", if found open the workbook and run application "V3_NewDeltaVH_Sort" and then close at the end. If both not found then show "No production Build.........".. So How should I modify or add in the macro below :- Private Sub V3DVH1stPassYield_Click() 'V3DVH1stPassYield_Click 'Recorded and Modified on 16 Aug 2005 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Range("A4:L2500").ClearContents Range("L2505").Select Selection.ClearContents Range("L2505").Select Selection.Interior.ColorIndex = xlNone If Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then Range("L2505") = "No Production Build or No Datas Found. PLS VERIFY" Range("L2505").Select Selection.Interior.ColorIndex = 45 Else Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") & ".csv" Application.Run "V3_NewDeltaVH_Sort" ActiveSheet.Range("A2:L2498").Select Selection.Copy Workbooks("DVH Template C Macro V3.xls").Activate ActiveSheet.Name = "DVH V3 1st Pass" Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Windows("H73FJ" & Range("J2516") & ".csv").Activate Application.CutCopyMode = False ActiveWindow.Close SaveChanges:=False End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Range("A2501").Select End Sub ddiicc |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
re : Help Need on my codes
You can use the DIR function to check for the existence of the files.
Sub FindFiles() Dim File1 As String Dim File2 As String Dim FF1 As String Dim FF2 As String File1 = "C:/Temp/H73FJ.xls" '<<change as required File2 = "C:/Temp/H72Fj.xls" '<<change as required FF1 = Dir(File1) FF2 = Dir(File2) If FF1 < "" Then Workbooks.Open Filename:=File1 Call V3_NewDeltaVH_Sort Windows(FF1).Close 'add savechanges if required End If If FF2 < "" Then Workbooks.Open Filename:=File2 Call V3_NewDeltaVH_Sort Windows(FF2).Close 'add savechanges if required End If If FF1 = "" And FF2 = "" Then MsgBox "No Production build" End If End Sub Hope this helps Rowan "ddiicc" wrote: Hi Experts, I have a macro written below, it is able to find the file name "...H73FJ...", then open the file and continue and then able to close the file name that is being opened. But I have a problem which is .... I may have 2 types of file name in the database, eg... " ...H73FJ..." and "....H72FJ...." So I would like my macros to do this.... to find file name "...H72FJ..." if found, open the workbook and run application "V3_NewDeltaVH_Sort" and then close at the end, if not found, continue to find file name "....H73FJ....", if found open the workbook and run application "V3_NewDeltaVH_Sort" and then close at the end. If both not found then show "No production Build.........".. So How should I modify or add in the macro below :- Private Sub V3DVH1stPassYield_Click() 'V3DVH1stPassYield_Click 'Recorded and Modified on 16 Aug 2005 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Range("A4:L2500").ClearContents Range("L2505").Select Selection.ClearContents Range("L2505").Select Selection.Interior.ColorIndex = xlNone If Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then Range("L2505") = "No Production Build or No Datas Found. PLS VERIFY" Range("L2505").Select Selection.Interior.ColorIndex = 45 Else Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") & ".csv" Application.Run "V3_NewDeltaVH_Sort" ActiveSheet.Range("A2:L2498").Select Selection.Copy Workbooks("DVH Template C Macro V3.xls").Activate ActiveSheet.Name = "DVH V3 1st Pass" Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Windows("H73FJ" & Range("J2516") & ".csv").Activate Application.CutCopyMode = False ActiveWindow.Close SaveChanges:=False End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Range("A2501").Select End Sub ddiicc |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
re : Help Need on my codes
Rowan,
Thanks alot ... it works very well...:))))) "Rowan" wrote: You can use the DIR function to check for the existence of the files. Sub FindFiles() Dim File1 As String Dim File2 As String Dim FF1 As String Dim FF2 As String File1 = "C:/Temp/H73FJ.xls" '<<change as required File2 = "C:/Temp/H72Fj.xls" '<<change as required FF1 = Dir(File1) FF2 = Dir(File2) If FF1 < "" Then Workbooks.Open Filename:=File1 Call V3_NewDeltaVH_Sort Windows(FF1).Close 'add savechanges if required End If If FF2 < "" Then Workbooks.Open Filename:=File2 Call V3_NewDeltaVH_Sort Windows(FF2).Close 'add savechanges if required End If If FF1 = "" And FF2 = "" Then MsgBox "No Production build" End If End Sub Hope this helps Rowan "ddiicc" wrote: Hi Experts, I have a macro written below, it is able to find the file name "...H73FJ...", then open the file and continue and then able to close the file name that is being opened. But I have a problem which is .... I may have 2 types of file name in the database, eg... " ...H73FJ..." and "....H72FJ...." So I would like my macros to do this.... to find file name "...H72FJ..." if found, open the workbook and run application "V3_NewDeltaVH_Sort" and then close at the end, if not found, continue to find file name "....H73FJ....", if found open the workbook and run application "V3_NewDeltaVH_Sort" and then close at the end. If both not found then show "No production Build.........".. So How should I modify or add in the macro below :- Private Sub V3DVH1stPassYield_Click() 'V3DVH1stPassYield_Click 'Recorded and Modified on 16 Aug 2005 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Range("A4:L2500").ClearContents Range("L2505").Select Selection.ClearContents Range("L2505").Select Selection.Interior.ColorIndex = xlNone If Dir$(Range("I2513") & "H73FJ" & Range("J2516") & ".csv") = "" Then Range("L2505") = "No Production Build or No Datas Found. PLS VERIFY" Range("L2505").Select Selection.Interior.ColorIndex = 45 Else Workbooks.Open Filename:=Range("I2513") & "H73FJ" & Range("J2516") & ".csv" Application.Run "V3_NewDeltaVH_Sort" ActiveSheet.Range("A2:L2498").Select Selection.Copy Workbooks("DVH Template C Macro V3.xls").Activate ActiveSheet.Name = "DVH V3 1st Pass" Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Windows("H73FJ" & Range("J2516") & ".csv").Activate Application.CutCopyMode = False ActiveWindow.Close SaveChanges:=False End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Range("A2501").Select End Sub ddiicc |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 codes in 1 | Excel Discussion (Misc queries) | |||
| Zip Codes | New Users to Excel | |||
| VBA codes | Excel Discussion (Misc queries) | |||
| codes, codes, codes... | Excel Discussion (Misc queries) | |||
| where are my VB codes | Excel Programming | |||
Linear Mode
