VBA Help

[VbaHell]

Hello All

I hope you can help on this.

I have some VBA code that opens a file C:\tmp\SOSAX.CSV
If the file is not there the code Bugs out.
Is it possible that if this error occurs a Message box opens up saying "Export SOSAX file first" instead of the error popup.

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\tmp\SOSAX.CSV"
Range("A1:O500").Select
Selection.Copy
Windows("Energy CVI.xls").Activate
Sheets("Data").Select
Range("B6").Select
ActiveSheet.Paste
Windows("SOSAX.CSV").Activate
ActiveWindow.Close
ThisWorkbook.Saved = True

 

[shg]

One way:

    Const sFile As String = "C:\tmp\SOSAX.CSV"
    
    If Len(Dir(sFile)) Then
        Workbooks.Open Filename:=sFile
    Else
        MsgBox "No file!"
        Exit Sub
    End If

 

[VbaHell]

Thank you so much
Where in my code would you insert this please

 

[shg]

Sub x()
    Const sFile     As String = "C:\tmp\SOSAX.CSV"
 
    If Len(Dir(sFile)) Then
        With Workbooks.Open(Filename:=sFile)
            Range("A1:O500").Copy _
                    Workbooks("Energy CVI.xls").Worksheet("Data").Range("B6")
            .Close SaveChanges:=False
            ThisWorkbook.Saved = True
        End With

    Else
        MsgBox "No file!"
    End If
End Sub

 

[VbaHell]

Hello again, I know I am now being a pain but I have spent several hours trying to make this work so I am hoping you can assist me again.
Below is all the code that I am running which is activated by a Button.
I have tried to use your code and still have the popup Debug box

Sub Update()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\tmp\SOSAX.CSV"
Range("A1:O500").Select
Selection.Copy
Windows("Energy CVI.xls").Activate
Range("B6").Select
Sheets("Data").Select
Range("B6").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.NumberFormat = "0.00"
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Windows("SOSAX.CSV").Activate
ActiveWindow.Close
ThisWorkbook.Saved = True

End Sub