Error message

[Les Stout]

Hello all, i have the code below which i got from this site and it works
great, just if cancel is selected i get an error and i would then like
to give a message asking if they would really like to cancel, but im not
sure where to put it in, could some body please help ?


Sub GetDLoadFromSapStd()

Dim mySavedPath As String
Dim Cancel As Variant
mySavedPath = CurDir

ChDirNet "\\zadad01\sapinter\ZA-TM-RECON\DOWNLOAD"
fileToOpen = Application _
.GetOpenFilename("Text Files (*.csv),*.csv") mySapFile =
fileToOpen
Workbooks.OpenText FileName:=mySapFile
ChDirNet mySavedPath
OptionCheckStd
End If
End Sub


Les Stout

 

[Edward Ulle]

Try this.

Sub GetDLoadFromSapStd()

Dim mySavedPath As String
' Dim Cancel As Variant ' Dont see where needed
mySavedPath = CurDir

ChDirNet "\\zadad01\sapinter\ZA-TM-RECON\DOWNLOAD"
' ========== Insert from here =======
TryAgain:
fileToOpen = Application.GetOpenFilename("Text Files (*.csv),*.csv")
If fileToOpen = "False" Then
If MsgBox("Do you want to cancel?", vbYesNo) = vbYes Then
ChDirNet mySavedPath
Exit Sub
Else
GoTo TryAgain
End If
End If
' ========== to here =========
mySapFile = fileToOpen
Workbooks.OpenText Filename:=mySapFile
ChDirNet mySavedPath
OptionCheckStd
' End If ' Dont see where needed
End Sub

 

[Dave Peterson]

One more way:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Sub GetDLoadFromSapStd()

Dim mySavedPath As String
Dim FileToOpen As Variant
Dim mySapFile As Variant
Dim Cancel As Variant
mySavedPath = CurDir

On Error Resume Next
ChDirNet "\\zadad01\sapinter\ZA-TM-RECON\DOWNLOAD"
If Err.Number <> 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If

FileToOpen = Application _
.GetOpenFilename("Text Files (*.csv),*.csv")

'one more check
if filetoopen = false then
'user hit cancel
exit sub
end if

mySapFile = FileToOpen
Workbooks.OpenText Filename:=mySapFile
ChDirNet mySavedPath
'OptionCheckStd
'End if
End Sub

 

[Les Stout]

Thanks Edward and Dave for your input.

best regards,

Les Stout