Solved

Using Data.xls as backend to a vb 6 application

Posted on 2004-10-25
479 Views
Last Modified: 2006-11-17
I am using an excel file (Data.xls) to store smal number of data in it (don't want to use Access).

Preparation to test the code prior to submision:
-  Please make two excel files (both blank, no data) and call them Data.xls and MyTestFile.xls
-  Enter the name of the second file ("MyTestFile.xls") in any cell in Data.xls

Code required:
1. To test read capability, read the name of the second excel, "MyTestFile.xls" from Data.xls, and store it in strFileName.
2. Open excel file strored in strFileName (in other words open MyTestFile.xls).
    After strFileName opens, type something in it, say "ABC" in any cell in this file, MyTestFile.xls
3. Now, to test its write capability, copy the content of above cell ("ABC") from MyTestFile.xls to any cell in Data.xls
4. After testing above procedures, modify the code to make Data.xls not to show up (user doesn't see when Data.xls opens or closes).

Regards,

Mike
0
Question by:Mike Eghtebas
    6 Comments
     
    LVL 16

    Expert Comment

    by:jimbobmcgee
    Out of interest, why do you want two XLS files?  If you are using an XLS for your data file, perhaps you could use an ADODB.Recordset?

    J.
    0
     
    LVL 16

    Accepted Solution

    by:
    A simple VB app, with a reference to Microsoft Excel Object, should do it (1 x Form, 1 x Button):

        Const strDataXLS As String = "d:\Data.xls"
       
        Public objXL1 As Excel.Application
        Public objXL2 As Excel.Application
        Public objWB1 As Excel.Workbook
        Public objWB2 As Excel.Workbook
       
       
        Sub Form1_Load()
       
            'LOAD THE DATA FILE WHEN APPLICATION STARTS
       
            Dim strFileName As String
           
            Set objXL1 = CreateObject("Excel.Application")
                                        'CREATE HANDLE TO EXCEL
           
            Set objXL2 = CreateObject("Excel.Application")
                                        'CREATE ANOTHER HANDLE TO EXCEL
           
            Set objWB1 = objXL1.Workbooks.Open(strDataXLS)
                                        'OPEN DATA FILE IN FIRST EXCEL
           
                strTestXLS = objWB1.Sheets(1).Range("A1").Value
                                        'READ TEST FILENAME FROM CELL A1 IN DATA FILE
       
            Set objWB2 = objXL2.Workbooks.Open(strFileName)
                                        'OPEN TEST FILE IN SECOND EXCEL
                               
            objXL1.Visible = False      'HIDE FIRST EXCEL
            objXL2.Visible = True       'SHOW SECOND EXCEL
       
        End Sub
       
       
        Sub Command1_Click()
       
            'UPDATE THE DATA FILE, ON 'UPDATE' BUTTON CLICK
       
            Dim objCell As Excel.Range
           
            For Each objCell In objWB2.Sheets(1).Cells
           
                If objCell.Value <> objWB1.Sheets(1).Cells(objCell.Row, objCell.Column).Value Then
                   
                    'IF CELLS ARE DIFFERENT, OVERWRITE THEM
                    objWB1.Sheets(1).Cells(objCell.Row, objCell.Column) = objCell.Value
                   
                End If
               
            Next
           
            objWB1.Save
       
        End Sub
       
       
        Sub Form1_QueryUnload()
       
            'SUGGEST UPDATE AND UNLOAD EXCEL OBJECTS
           
            If MsgBox("Commit changes to " & strDataXLS & "?", vbYesNo Or vbQuestion) = vbYes Then
           
                'IF YES IS CHOSEN, PERFORM UPDATE ROUTINE
                Call Command1_Click
               
            End If
           
            objWB2.Close False          'CLOSE TEST FILE (DO NOT SAVE CHANGES)
            objWB1.Close False          'CLOSE DATA FILE (DO NOT SAVE CHANGES)
           
            objXL2.Quit                 'CLOSE SECOND EXCEL
            objXL1.Quit                 'CLOSE FIRST EXCEL
           
            Set objWB1 = Nothing
            Set objWB2 = Nothing
            Set objXL1 = Nothing
            Set objXL2 = Nothing        'CLEANUP
       
        End Sub


    The form opens and closes the data/test files on load/unload.  Use the button on the form to commit changes...

    HTH.

    J.
    0
     
    LVL 33

    Author Comment

    by:Mike Eghtebas
    Thank you Jim, I will give it a try.  It looks pretty good.

    Mike
    0
     
    LVL 44

    Expert Comment

    by:aikimark
    1. Why are you using Excel?
    2. Why not just use CSV format?  Both data formats are accessable via ADO.

    Note: automating Excel just to get data is a relative sloooow process with more overhead than required.
    0
     
    LVL 16

    Expert Comment

    by:jimbobmcgee
    >> Why are you using Excel?

    I think the plan is to allow editing in the familiar environment of Excel and to manage which edits are committed.  I would favour the ADO method, too...
    0
     
    LVL 33

    Author Comment

    by:Mike Eghtebas
    Thank you for the inputs.  I am looking into a link provided by jeverist in vb section

    http://www.erlandsendata.no/english/index.php?d=envbadacwbdbado

    It looks pretty good.  Now, I am trying to make it work for me.

    Mike
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    913 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

    15 Experts available now in Live!

    Get 1:1 Help Now