Trying to break out of While Loop. Need to capture relative path of selected file from Dialog box

Question

2.50/5 (2 votes)

See more:

Hello:

I wrote a procedure to import 5 files into an Excel workbook. If the file is not found on the directory, then the an OpenFileDialog pops up to allow the user to browse for the file. If the user selects a file other than the one that is supposed to be imported, a warning pops up. Since there is a possibility that users select the wrong file more than once, I wrote a While Loop to keep checking for the right file name.

Up to there everything worked well. Here is where the issue comes in, If the file is not on the expected directory, say is on a CD, the user browses and finds the right file, and tries to open it, I still get the error message that the file is not the correct one. I figure out why, the While Loop is correct but the way I set my variables up is where the issue is.

My files are supposed to be located in the same path as my workbook so I set up my variables as follows:

VB

Dim xlWBPath As String = Globals.ThisWorkbook.Application.ActiveWorkbook.Path
Dim strImportFile As String
strImportFile = xlWBPath & "\" & GetImportFiles(n)

But because my missing file may be on a different directory my variable

strImportFile

is no longer valid, I have to now get the new path of the selected file and change, or declare a new variable. I have tried to do that on my code, but it is not working. Below is my code, I highlighted the area with the trouble:

VB

Dim xlDestSheet As Excel.Worksheet
        Dim xlWBPath As String = Globals.ThisWorkbook.Application.ActiveWorkbook.Path
        Dim strImportFile As String
        Dim xlWBSource As Object = Nothing
        Dim xlWBImport As Object = Nothing


        'Loop through the 5 sheets and files

        For n As Long = 1 To 5 Step 1

            strImportFile = xlWBPath & "\" & GetImportFiles(n)
            xlDestSheet = DataSheets(n)

            'Convert the indexed sheet name to a string
            'so that it can be passed through the xlWB.Worksheets paramater

            Dim strDestSheetName As String = xlDestSheet.Name

            'If the file is found, then import, copy and paste the
            'data into the corresponding sheets
            If Len(Dir(strImportFile)) > 0 Then

                xlWBSource = Globals.ThisWorkbook.Application.ActiveWorkbook
                xlWBImport = Globals.ThisWorkbook.Application.Workbooks.Open(strImportFile)
                xlWBImport.Worksheets(1).Cells.Copy(xlWB.Worksheets(strDestSheetName).Range("A1"))
                xlWBImport.Close()

            Else

                'If a sheet is missing, prompt the user if they
                'want to browse for the file.

                'Messagbox variables
                Dim msbProceed As MsgBoxResult
                Dim strVmbProceedResults As String = ("Procedure Canceled. Your project will now close")
                Dim strPrompt As String = " source file does not exist." & vbNewLine & _
                    "Press OK to browse for the file or CANCEL to quit"

                'If the user does not want to browse, then close the workbook, no changes saved.
                msbProceed = MsgBox("The " & GetImportFiles(n) & strPrompt, MsgBoxStyle.OkCancel + MsgBoxStyle.Question, "Verify Source File")

                If msbProceed = MsgBoxResult.Cancel Then
                    msbProceed = MsgBox(strVmbProceedResults, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical)

                    xlWB.Close(SaveChanges:=False)

                    Exit Sub

                Else

                    'If the user does want to browse, then open the File Dialog
                    'box for the user to browse for the file

                    'Open Fil Dialog box variable and settings
                    Dim ofdGetOpenFileName As New OpenFileDialog()

                    ofdGetOpenFileName.Title = "Open File " & GetImportFiles(n)
                    ofdGetOpenFileName.InitialDirectory = xlWBPath
                    ofdGetOpenFileName.Filter = "Excel Files (*.xls;*.xlsx; *.xlsm; *.csv)| *.xls; *.csv; *.xlsx; *.xlsm"
                    ofdGetOpenFileName.FilterIndex = 2
                    ofdGetOpenFileName.RestoreDirectory = True

                    'If the user presses Cancel on the box, warn that no
                    'file has been selected and the workbook will close

                    If ofdGetOpenFileName.ShowDialog() = System.Windows.Forms.DialogResult.Cancel Then

                        'Message box variables
                        Dim msbContinue As MsgBoxResult
                        Dim strAlert As String = ("You have not selected a workbook. The project will now close without saving changes")

                        'Once the user presses OK, close the file and do not save changes
                        msbContinue = MsgBox(strAlert, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "No Workbook Selected")
                        xlWB.Close(SaveChanges:=False)

                        Exit Sub

                    Else

                        'If the user does select the file, then import the file
                        'make sure the right file was selected, if the wrong file is selected, issue warning
                        While strImportFile <> GetImportFiles(n)

                            Dim msbContinue As MsgBoxResult
                            Dim strAlert As String = ("The selected file is invalid. Please select file: " & GetImportFiles(n) & vbNewLine & _
                                                               " to continue.")
                            msbContinue = MsgBox(strAlert, MsgBoxStyle.RetryCancel + MsgBoxStyle.Critical, "Wrong File Selection")

                            If msbContinue = MsgBoxResult.Cancel Then
                                xlWB.Close(SaveChanges:=False)

                                Exit Sub

                            Else
                                ofdGetOpenFileName.ShowDialog()

'*****Here is where I try to change the value of my 
'*****variable, but my loop still does not break.


                                Dim strGetPath As String = Nothing
                                strGetPath = System.IO.Path.GetDirectoryName(ofdGetOpenFileName.FileName)

                                strImportFile = strGetPath & "\" & GetImportFiles(n)

                            End If

                            Continue While

                            If strImportFile = GetImportFiles(n) Then

                                Exit While

                                strImportFile = ofdGetOpenFileName.FileName
                                xlWBImport = Globals.ThisWorkbook.Application.Workbooks.Open(strImportFile)
                                xlWBImport.Worksheets(1).Cells.Copy(xlWB.Worksheets(strDestSheetName).Range("A1"))
                                xlWBImport.Close()

                            End If


                            Try

                                'Import the remainder of the files
                                xlWBSource = Globals.ThisWorkbook.Application.ActiveWorkbook
                                xlWBImport = Globals.ThisWorkbook.Application.Workbooks.Open(strImportFile)
                                xlWBImport.Worksheets(1).Cells.Copy(xlWB.Worksheets(strDestSheetName).Range("A1"))
                                xlWBImport.Close()

                            Catch

                                MsgBox(Err.Description, MsgBoxStyle.Critical, "Unexpected Error")

                            End Try
                        End While

      End If



                    End If

                End If



        Next



    End Sub

Posted 29-Sep-13 16:14pm

qbndl8

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kschuler · Accepted Answer · 2013-10-01T10:11:00

If the GetImportFiles() function only returns a file name, for example "myworkbook.xls" then when you compare it in the While statement you need to compare it to just a filename. You can't use strImportFile if you set that up to contain a full path, for example "C:\myworkbook.xls". Of course they don't match. So maybe you just need to add another variable that will contain JUST the file name so you can compare it to GetImportFiles(). You may want to check out System.IO.Path[^] class. It has a .GetFileName[^] method whith will return you just the filename and extension.

Also, you may want to check out the System.IO.File[^]class, which has a .Exists[^] that will return True or False if the file actually exists. It's always good to make sure the file is really there before working with it.

I would also suggest that if you are comparing filenames that you first .ToUpper them so that if the user had entered "myworkbook.xls" and you were looking for "MyWorkbook.XLS" it will still see it as a match.

And my last bit of advice is to still allow some other way for the user to get out of that loop. Perhaps they can't find the right file so they want to quit your program. I think you should add a variable to track if the user has cancelled the operation and that would also get you out of your while loop.

Hope this helps.