none
Choose a file VS2017 RRS feed

  • Question

  • Hi Guys,

     left this as one of the last bits of my project to come back to as thought it would be easy and Ive spent over a day on it so need to ask for help please.

    The need is for a user to be able to select a file and the path and file name to be stored in a string without the actual file opening up. Simple enough ask I thought using the 'Openfiledialog'. I use this in other place with code such as:

     
      Dim t As String = "Excel Files|*.xls;*.xlsx;*.xlsm"
                Dim fd = New OpenFileDialog()
                fd.Title = "Choose File"
                fd.Multiselect = False
                fd.InitialDirectory = "C:\"
                fd.Filter = t
                fd.FilterIndex = 2
                fd.RestoreDirectory = True 

    (Yep it can be simplified)

    The issue I have is when an excel workbook is password protected, once I choose the file it opens excel and prompts for the password.   Is there anyway to just choose the file, return the file info on a password protected file without it opening or prompting for the password.

    Would really appreciate some help as a day of googling, playing with code and trying to convert some old VBA just ha not helped.

    Kind Regards

    Scott 


    • Edited by Scott77_77 Monday, March 11, 2019 5:02 PM
    Monday, March 11, 2019 5:02 PM

All replies

  • Hi

    Something like this maybe?

        Dim s As String = GetFilePath()
        If Not s = Nothing Then
          ' got a file name
        Else
          ' no file name
        End If
    
    
    
      Function GetFilePath() As String
        ' I don't have any Excel files
        Dim t As String = "Text Files|*.txt"
        Dim fd As New OpenFileDialog()
        Dim result As DialogResult = fd.ShowDialog
        If result = DialogResult.OK Then
          fd.Title = "Choose File"
          fd.Multiselect = False
          fd.InitialDirectory = "C:\"
          fd.RestoreDirectory = True
          Return fd.FileName
        End If
        Return Nothing
      End Function


    Regards Les, Livingston, Scotland

    Monday, March 11, 2019 5:33 PM
  • To open a password protected Excel file this is the basic pattern using Excel automation.

    See more at the following code sample.

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Public Class ExcelOperations
        ''' <summary>
        ''' Open Excel file to specific sheet with a password
        ''' </summary>
        ''' <param name="pFileName">Full path and file name</param>
        ''' <param name="pSheetName">WorkSheet to work on</param>
        ''' <param name="pPassword">Excel file password</param>
        Public Sub OpenExcel(pFileName As String, pSheetName As String, pPassword As String)
            Dim proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(pFileName, [ReadOnly]:=False, Password:=pPassword)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For index = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(index), Excel.Worksheet)
    
                If xlWorkSheet.Name = pSheetName Then
                    proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
            If proceed Then
                '
                ' File is open to the proper WorkSheet
                '
            End If
    
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
    
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
    
        End Sub
        Public Sub ReleaseComObject(obj As Object)
            Try
                Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 11, 2019 5:45 PM
    Moderator
  • Hi Les, Hi Karen,

    Thank you for your replies.

    Les - Your solution does pretty much what mine does and opens excel and prompts the user for a password. I changed it to test to 

        Private Sub Button3_Click_1(sender As Object, e As EventArgs) Handles Button3.Click
            Dim s As String = GetFilePath()
            If Not s = Nothing Then
                ' got a file name
            Else
                ' no file name
            End If
    
        End Sub
    
        Function GetFilePath() As String
            ' I don't have any Excel files
            Dim t As String = "Excel Files|*.xls;*.xlsx;*.xlsm"
            Dim fd As New OpenFileDialog()
            Dim result As DialogResult = fd.ShowDialog
            If result = DialogResult.OK Then
                fd.Title = "Choose File"
                fd.Multiselect = False
                fd.InitialDirectory = "C:\"
                fd.RestoreDirectory = True
                Return fd.FileName
            End If
            Return Nothing
        End Function

    Hi Karen - Thank you for your reply and also all the contributions as I have referred to a lot in the past!  The Issue I have is not automating excel. but just choosing a file name. It just happens to be excel in this instance.

    So basically I want the user to select a file which I will hold the path and file name in a variable for later use. 

    So If the user clicks excel which would be 99% of the time. I can use the openfiledialog to allow the user to choose the file and I can capture the path and file and assign it to a variable.  The issue is when an excelfile is password protected. In this instance when the file is chosen Excel auto pops up and prompts for a password before I even ask it to open the file. A default behaviour of openfiledialog?

    So Is it possible to just choose the file return the variable but not have the excel open and password prompt for protected files.

    It sounds like it should be easy.... But its driving me nuts :)

    Kind Regards

    Scott

    Monday, March 11, 2019 6:09 PM
  • Hi Les, Hi Karen,

    Thank you for your replies.

    Les - Your solution does pretty much what mine does and opens excel and prompts the user for a password. I changed it to test to 

        Private Sub Button3_Click_1(sender As Object, e As EventArgs) Handles Button3.Click
            Dim s As String = GetFilePath()
            If Not s = Nothing Then
                ' got a file name
            Else
                ' no file name
            End If
    
        End Sub
    
        Function GetFilePath() As String
            ' I don't have any Excel files
            Dim t As String = "Excel Files|*.xls;*.xlsx;*.xlsm"
            Dim fd As New OpenFileDialog()
            Dim result As DialogResult = fd.ShowDialog
            If result = DialogResult.OK Then
                fd.Title = "Choose File"
                fd.Multiselect = False
                fd.InitialDirectory = "C:\"
                fd.RestoreDirectory = True
                Return fd.FileName
            End If
            Return Nothing
        End Function


    Hi,

    Do you solve the issue?I think you should fix your code:

      Private Sub Button3_Click_1(sender As Object, e As EventArgs) Handles Button3.Click
            Dim s As String = GetFilePath()
            If Not s = Nothing Then
                ' got a file name
            Else
                ' no file name
            End If
    
        End Sub
    
        Function GetFilePath() As String
            ' I don't have any Excel files
            Dim t As String = "Excel Files|*.xls;*.xlsx;*.xlsm"
            Dim fd As New OpenFileDialog()
            fd.Title = "Choose File"
             fd.Multiselect = False
             fd.InitialDirectory = "C:\"
             fd.RestoreDirectory = True
            Dim result As DialogResult = fd.ShowDialog
            If result = DialogResult.OK Then
               
                Return fd.FileName
            End If
            Return Nothing
        End Function

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 12, 2019 6:23 AM
  • Hi Alex,

    Thank you for your time in replying

    unfortunately it did not. The prompt to enter the password pops at up at  'Dim result As DialogResult = fd.ShowDialog' so before I can capture it in some kind of handler. 

    I tried messing about with an IO.directory method last night but got tired so may pick that up today.

    If I can't get the file chooser working I will just have to make it a bit more clunky and have the user choose a folder 1st then return all the files in the folder to a combobox for them to choose from the list. Not ideal but it's a work around.

    All the heavy code on this part of the project is written and I worked off a hard coded file for development purposes thinking adding a file chooser would be a 5 minute job on tidy up and error handing which I will add towards the end, how wrong I was. :)

    Regards

    Scott

    Note for anyone else trying to replicate:

    - Save a password protected excel workbook somewhere.

    - in VS using filedialog or other method browse to the file you saved and choose it so you can just pull the path and file name to a variable. BUT without excel opening and/or the password prompt opening up.


    • Edited by Scott77_77 Tuesday, March 12, 2019 7:38 AM
    Tuesday, March 12, 2019 7:38 AM