Cancel Button When Importing File

[helpexcel]

I'm using the following code to import a another file, I thought I fixed the issue of getting a runtime error, but now it doesn't open the selected file and I get the MSG Box.

Dim sPath As String
Dim sFile


Dim ws1 As Worksheet
Set ws1 = Sheet1


sPath = "\\Address\Folder1\Folder2\Folder3" & ws1.Range("A2")
If SetFilePath(sPath) = 0 Then
MsgBox "Error in setting the path - " & sPath
Else
sFile = Application.GetOpenFilename("Excel,*.xls;*.xlsx")
If sFile <> False Then Set WB = Workbooks.Open(sFile) Else
MsgBox "No File Selected"
Exit Sub

End If

 

[BlueAure]

Hi helpexcel,

Which msgbox is displaying? Is your SetFilePath function returning a 0? Or is sFile not equal to False? Please place your code in code brackets by clicking the # button at the top right of your editor.

Also, it seems to me that you are missing an End If.

Dim sPath As String
Dim sFile

Dim ws1 As Worksheet
Set ws1 = Sheet1

sPath = "\\Address\Folder1\Folder2\Folder3" & ws1.Range("A2")


If SetFilePath(sPath) = 0 Then
    MsgBox "Error in setting the path - " & sPath
Else
    sFile = Application.GetOpenFilename("Excel,*.xls;*.xlsx")
    If sFile <> False Then
        Set wb = Workbooks.Open(sFile)
    Else
        MsgBox "No File Selected"
        Exit Sub
    [COLOR=#ff0000]End If [/COLOR]'You need this here I think


End If

 

[helpexcel]

The second MSG Box shows. Basically I want that box to show and exit sub if the cancel button is hit on the file select dialogue box.


Sorry - I have the 2nd End If, I just cut it off while copy/pasting

[COLOR=#333333]Dim sPath As String[/COLOR]
[COLOR=#333333]Dim sFile[/COLOR]


[COLOR=#333333]Dim ws1 As Worksheet[/COLOR]
[COLOR=#333333]Set ws1 = Sheet1[/COLOR]


[COLOR=#333333]sPath = "\\Address\Folder1\Folder2\Folder3" & ws1.Range("A2")[/COLOR]
[COLOR=#333333]If SetFilePath(sPath) = 0 Then[/COLOR]
[COLOR=#333333]MsgBox "Error in setting the path - " & sPath[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]sFile = Application.GetOpenFilename("Excel,*.xls;*.xlsx")[/COLOR]
[COLOR=#333333]If sFile <> False Then Set WB = Workbooks.Open(sFile) Else[/COLOR]
[COLOR=#333333]MsgBox "No File Selected"[/COLOR]
[COLOR=#333333]Exit Sub[/COLOR]

[COLOR=#333333]End If
[/COLOR]

 

[BlueAure]

This code works for me. Are you selecting a file manually or via sPath?

 

[helpexcel]

OK, I reordered the code and now it's working. Not sure why i need to put the "Then" and "Else" on different lines. The cancel part was working, but the import was getting stuck and displaying the message also.

Dim sPath As String
Dim sFile


Dim ws1 As Worksheet
Set ws1 = Sheet1


sPath = "\\Address\Folder1\Folder2\Folder3\" & ws1.Range("A2")


If SetFilePath(sPath) = 0 Then
    MsgBox "Error in setting the path - " & sPath
Else
    sFile = Application.GetOpenFilename("Excel,*.xls;*.xlsx")
End If




If sFile <> False Then
    Set WB = Workbooks.Open(sFile)
Else
    MsgBox "No File Selected"
        Exit Sub
End If