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

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

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