Solved

Open excell file using filedialog and fill data on it

Posted on 2016-09-24
6
37 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 18
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now