DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

open existing excel file in vba

•

Join Date: Sep 2007

Posts: 6

Reputation: six888 is an unknown quantity at this point

Solved Threads: 0

six888

Offline

Newbie Poster

open existing excel file in vba

Oct 10th, 2007

im new in macro programming and vb
im doing a project office automation
how do i open (without showing) existing excel?
so i can manipulate the data

most of the snipet i see in the net is like this

Set oExcel = CreateObject("Excel.Application")
Set ExcelDoc = oExcel.Workbooks.Open(m_strPath & "project.xls")

i want the user to choose which excel file to edit
so a dialog box should prompt out
im thinking of using a dialog box
but i couldnt find any dialogbox button in vb 6

please help me
thanks in advance

•

Join Date: Aug 2007

Posts: 160

Reputation: kb.net is an unknown quantity at this point

Solved Threads: 26

kb.net

Offline

Junior Poster

Re: open existing excel file in vba

Oct 11th, 2007

Hi,

Check below example, for it to work add the component "Microsoft Common Dialouge Control" and add one to your form:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Private Sub Command1_Click()
 
Dim xl
Dim xlsheet
Dim xlwbook
Dim FilNam As String
 
    Me.CommonDialog1.ShowOpen
    FilNam = Me.CommonDialog1.FileName
 
    If Me.CommonDialog1.FileName <> "" Or Me.CommonDialog1.FileName <> ".xls" Then
 
        Set xl = CreateObject("Excel.Application")
        Set xlwbook = xl.Workbooks.Open(FilNam)
        Set xlsheet = xlwbook.Sheets.Item(1)
        xl.Visible = True 'Remove to hide the excel file
 
        Me.Text1 = xlsheet.range("A1") 'Do your work
 
        Set xl = Nothing
        Set xlwbook = Nothing
        Set xlsheet = Nothing
 
    End If
 
End Sub
Private Sub Command1_Click()

Dim xl
Dim xlsheet
Dim xlwbook
Dim FilNam As String

    Me.CommonDialog1.ShowOpen
    FilNam = Me.CommonDialog1.FileName

    If Me.CommonDialog1.FileName <> "" Or Me.CommonDialog1.FileName <> ".xls" Then
    
        Set xl = CreateObject("Excel.Application")
        Set xlwbook = xl.Workbooks.Open(FilNam)
        Set xlsheet = xlwbook.Sheets.Item(1)
        xl.Visible = True 'Remove to hide the excel file
        
        Me.Text1 = xlsheet.range("A1") 'Do your work
        
        Set xl = Nothing
        Set xlwbook = Nothing
        Set xlsheet = Nothing

    End If

End Sub

It is never about the number of languages you know, you either have the logic of programming or you don't ...

Some of the codes I post are collected from different sites during the past couple of years, so I would like to thank them for their help and for enabling me to help.