How to Make an If Statement go to a line of code

[JungleJme]

SOLVED How to Make an If Statement go to a line of code

Hi, i am getting quite a way into my module now! So first of all, thank you to everyone on here and over the rest of the internet for your help.

I am trying to write an if statement that allows the user to go back and select a file again in case they selected the wrong one the first time.

My question is how do i sort my if statement so it sends the user back a step to the "Please Browse" stage - however, at the moment if you click "Yes" when it asks to select another file it executes the exit application part anyway.

Anyone know how i need to phrase the if statements?

Dim SQLFile As String
SQLFile = Application.GetOpenFilename(, , "Please Browse to ABCNTSTR SQL Download", , False)

importsqlcheck = MsgBox("Are you sure you want to import this file?" & Chr$(13) & SQLFile, vbYesNo, "Confirm File to Import")

If importsqlcheck = vbNo Then
importtryagain = MsgBox("Update Failed. Select Another File?", vbYesNo, "Retry?")

Else
End If
If importryagain = vbYes Then

'???????????????????????????????????????????????

Else
Application.DisplayAlerts = False
ExpiredReport.Close savechanges = False
MsgBox ("Update Failed")
Application.Quit
End

End If

 

[vbaInet]

Put the call as a separate function and call the function when needed.

 

[JungleJme]

Thanks! Will have a crack at that and report back. Ta

 

[JungleJme]

OK, so i did what you said and created a function for the user to select a file to open

Function OpenFile()
Dim FileName As String
FileName = Application.GetOpenFilename("Excel Files (*.xls), *xls", , "Please Browse to File", , False)
If FileName = "" Or False Then
MsgBox "Update Failed!"
Application.Quit
Else
End If
End Function

However, this has set me back as originally what i wanted was to define a filename as a variable so i didn't have to hardcode it.

So, the function now defines the filename - how do i reference "Filename" in the rest of my module?

So i can call the function in my code, end the function but save the filename to use in the rest of my module?

Thanks in advance for any help??

 

[pr2-eugin]

Declare the FileName as a public string in the module then you can use it everywhere.. It should go on top of the module as.. which mean you have to remove the Dim declaraion inside the function..

Public FileName As String

 

[JungleJme]

Hi, thanks but i still get an error?

Public Filename as string
(various code) then:

If userdialog = vbcancel then
msgbox "You cancelled"
application.quit
Else
Call OpenFile()

' That goes to the function which is defined at the top of my module

Function OpenFile()
FileName = Application.GetOpenFilename("Excel Files (*.xls), *xls", , "Please Browse to File", , False)
If FileName = "" Or False Then
MsgBox "Update Failed!"
Application.Quit
Else
End If
End Function

' The function completes and then it returns to my code which continues:

Dim SQLFile as Workbook
SQLFile = Filename

Workbooks.Open SQLFile

'code continues to work on workbook

The error says that the variable is not set. Is the function not setting Filename as the selected file?

 

[JungleJme]

EDIT: Filename is returning the path to my file when i hover over it, its SQLFile that is not working.

I think therefore my method for trying to open Filename is wrong...

 

[JungleJme]

Got it. No need to rename it again, just do workbooks.open filename

Cheers Thanks for the help

 

[vbaInet]

I knew you were going to figure it out. Good job!

Also for next time, if you have an Excel VBA question, there's an Excel section of the forum where it should go. This section is Access VBA.

 

[JungleJme]

Thanks! It must be like watching bambi learn to walk.....

Noted. Will do!