[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Open excell file using filedialog and fill data on it

Posted on 2016-09-24
6
Medium Priority
?
77 Views
Last Modified: 2016-09-25
Hello everyone..  I want to chnage this code to use filedialog open..  Not declare the file in the procedure..  I want to chnage it to open anyworkbook


 Private Sub Button1_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)
        'edit the cell with new value
        xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub
0
Comment
Question by:Nassim am
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 22
ID: 41814091
Reference the Microsoft Office Library if you are using early binding (I am using 2016 -- your version may be different than the example). VBE Reference Microsoft Office Object Library
Then here is some code you can modify to suit your needs (it returns path\filename):
Function GetFile_Browse( _
   Optional psPathFile As String = "" _
   , Optional psDirectory As String = "" _
   , Optional psTitle As String = "" _
   , Optional pFilters As String = "XLS*" _
   , Optional psReturnFilenameOnly As String _
   ) As String
   
'Requires reference to Microsoft Office #.0 Object Library.
's4p 130325...141204
'
   'PARAMETERS
   ' psPathFile -- if sent, will be parsed for the start directory
   ' psDirectory -- if psPathFile not sent, specifies start directory. default is the FE directory.
   ' psReturnFilenameOnly - returns the name of the file stripped from the path if sent
   ' psTitle = Titlebar of the dialog box
   ' pFilters -- pFilters = A then  filtered for Access Files otherwise as specified
   
   On Error GoTo Proc_Err

   'initialize return value
   GetFile_Browse = ""
   
   Dim fDialog As Object 'late binding
'   Dim fDialog As Office.FileDialog 'early binding
   Dim varFile As Variant
   
   Dim sPathFile As String _
      , sStr As String _
      , nPos As Long
      
   If Len(psPathFile) > 0 Then
      'get the directory from psPathFile
      nPos = InStrRev(psPathFile, "\")
      If nPos > 0 Then
         psDirectory = Left(psPathFile, nPos)
      Else
         sPathFile = CurrentProject.Path & "\"
      End If
   Else
      If Len(psDirectory & "") > 0 Then
         'starting directory was specified, psDirectory
         sPathFile = psDirectory
      Else
         'psDirectory not specified, use FE directory
         sPathFile = CurrentProject.Path & "\"
      End If
   End If
         
   With Application.FileDialog(3) 'msoFileDialogFilePicker
      .Filters.Clear
      'use pFilter
      .Filters.Add pFilters & " Files" _
                  , "*." & pFilters

     'add all files as another option
      .Filters.Add "All Files", "*.*"
      
      If Len(psTitle) > 0 Then
         sStr = psTitle
      Else
         sStr = "Pick a File"
      End If
      .title = sStr
      .InitialFileName = sPathFile
      .AllowMultiSelect = False
      
      If .Show = True Then
         sPathFile = .SelectedItems(1)
      Else
         Exit Function
      End If
   
   End With 'fDialog

   '-------- set return filename
   psReturnFilenameOnly = ""
   nPos = InStrRev(psPathFile, "\")
   If nPos > 0 Then
      psReturnFilenameOnly = Mid(psPathFile, nPos + 1)
   End If
      
   GetFile_Browse = sPathFile
   
Proc_Exit:
   On Error Resume Next
   Set fDialog = Nothing
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetFile_Browse"

   Resume Proc_Exit
   Resume
End Function 

Open in new window

if what is returned is "" then exit the procedure.  Otherwise, specify it to open instead of "c:\test1.xlsx" in xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
1
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41814121
Simple example:

  Dim objFileDialog                                 As Object ' or As FileDialog (if using Early Binding)
  
  Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
  
  objFileDialog.Show
  
  If objFileDialog.SelectedItems.Count = 1& Then
     MsgBox objFileDialog.SelectedItems(1), vbInformation Or vbOKOnly, CurrentDb.Name
  End If
  
  Set objFileDialog = Nothing

Open in new window

0
 

Author Comment

by:Nassim am
ID: 41814156
Thx All for reply me..  

Sorry i'm not pro

I could'nt to edit the original code as i want... ( open file by filedialog ..  In place of specify file)  

This the original one. Can anyone edit it please?  
What i should change

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).value)
        'edit the cell with new value
        xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41814220
a standard function would be created that browses for a file and returns the path\file to your program, which will call it.

**********************************************************
*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up
Option Explicit ' require variable declaration

Open in new window

1
 

Author Comment

by:Nassim am
ID: 41814456
I will try this.. Thank you :)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41814463
Existing Topics: MS Access, MS Excel
Added Topic:  Visual Basic.NET
1

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question