Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using Data.xls as backend to a vb 6 application

Posted on 2004-10-25
6
Medium Priority
?
490 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
Comment
Question by:Mike Eghtebas
[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
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12408908
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:
jimbobmcgee earned 2000 total points
ID: 12409074
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 34

Author Comment

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

Mike
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 46

Expert Comment

by:aikimark
ID: 12416483
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
ID: 12417137
>> 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 34

Author Comment

by:Mike Eghtebas
ID: 12417310
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

618 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