• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

Using Data.xls as backend to a vb 6 application

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
Mike Eghtebas
Asked:
Mike Eghtebas
  • 3
  • 2
1 Solution
 
jimbobmcgeeCommented:
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
 
jimbobmcgeeCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you Jim, I will give it a try.  It looks pretty good.

Mike
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
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
 
jimbobmcgeeCommented:
>> 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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now