Create Excel-sheet with VB4 ?? 
Author Message
 Create Excel-sheet with VB4 ??

Create Excel-sheet with VB4 ??

I'm a beginner in VB4-programming and I'm looking for some help ...

I have an Excel-VB-macro which has to be transformed to VB to make an
exe-file from. The problem is, I need to read and write data from/to
Excel-worksheets.

Is it possible to create an Excel-worksheet, with VB4, to open an
Excel-worksheet or to read/write data from/to an Excel-worksheet ??

If it's possible, how does it work?

Thank you a lot
Dirk
        Thermo On-Line



Tue, 19 Jan 1999 03:00:00 GMT  
 Create Excel-sheet with VB4 ??

I think the best thing is to open Excel within VB and use DDE. The following
article is from the VB Knowledge Base.

Good Luck

Guido Brink
Den Haag
Netherlands

---------------------------------------------------------------------
The information in this article applies to:

- Standard and Professional Editions of Microsoft Visual Basic for
  Windows, versions 2.0 and 3.0
- Microsoft Visual Basic programming system for Windows, version 1.0
- Microsoft Excel for Windows, version 5.0
---------------------------------------------------------------------

SUMMARY
=======

This article describes how to initiate a dynamic data exchange (DDE)
conversation between a Visual Basic destination application and a
Microsoft Excel source application.

This article demonstrates how to:

 - Prepare a Microsoft Excel for Windows document for active DDE.
 - Initiate a manual DDE link (information updated upon request from the
   destination) between Visual Basic (the destination) and Excel (the
   source).
 - Use the LinkRequest method to update information in Visual Basic (the
   destination) based on information contained in Excel (the source).

 - Initiate a automatic DDE link (information updated automatically from
   source to destination) between Visual Basic (the destination) and Excel
   (the source).
 - Use the LinkPoke method to send information from Visual Basic (the
   destination) to Excel (the source).
 - Change the LinkMode property between automatic and manual.

MORE INFORMATION
================

A destination application sends commands through DDE to the source
application to establish a link. Through DDE, the source provides data to

the destination at the request of the destination or accepts information
at the request of the destination.

The procedure below is as an example showing how to establish a DDE
conversation between Visual Basic and Excel for Windows.

STEP ONE: Create the Source Spreadsheet in Excel
------------------------------------------------

1. Start Excel. A document (spreadsheet) with Sheet1 as the title is
   created by default.

2. From the File menu, choose Save As, and save the document (spreadsheet)

   naming it SOURCE.XLS

4. Exit Excel. For this example to function properly, Excel must not
   be loaded and running.

STEP TWO: Create the Destination Application in Visual Basic
------------------------------------------------------------

The destination is the application that performs the link operations. It
prompts the source to send information or informs the source that
information is being sent to it.

1. Start Visual Basic (VB.EXE). Form1 is created by default.

2. Add the following controls to Form1, and give them the properties
   indicated:

   Default Name   Caption            Name
   -------------------------------------------------
   Text1          (not applicable)   Text1
   Option1        Manual Link        ManualLink
   Option2        Automatic Link     AutomaticLink
   Command1       Poke               Poke
   Command2       Request            Request

3. Add the following code to the general Declaration section of Form1:

   Const AUTOMATIC = 1
   Const MANUAL = 2
   Const NONE = 0

4. Add the following code to the Load event procedure of Form1:

   Sub Form_Load ()
      'This procedure starts Excel and loads SOURCE.XLS, the
      'spreadsheet created above.
      Dim ErrorTries As Integer
      ErrorTries = 0
      On Error GoTo errorhandler

      z% = Shell("c:\EXCEL\excel SOURCE.XLS", 1)

      DoEvents         'Process Windows events to ensure that
                       'Excel executes before making any attempt

                       'to perform DDE.

      Text1.LinkMode = NONE    'Clear DDE link if it already exists.

      'Set up link with Excel:
      'Unremark the next line for use with Excel 4.
      'Text1.LinkTopic = "Excel|source.xls"

      'Remark out the next line when using Excel 4
      Text1.LinkTopic = "Excel|C:\VB3\[SOURCE.XLS]Sheet1"

      Text1.LinkItem = "R1C1"  'Set link to first cell on spreadsheet.
      Text1.LinkMode = MANUAL  'Establish a manual DDE link.

      ManualLink.Value = True
      Exit Sub

   errorhandler:
      If Err = 282 And ErrorTries < 15 Then
         ErrorTries = ErrorTries + 1
         DoEvents
         Resume
      Else
         Error Err
      End If

   End Sub

5. Add the following code to the Click event procedure of the Manual Link
   button:

   Sub ManualLink_Click ()
      Request.Visible = TRUE    'Make request button valid.
      Text1.LinkMode = NONE     'Clear DDE Link.
      Text1.LinkMode = MANUAL   'Reestablish new LinkMode.

   End Sub

6. Add the following code to the Click event procedure of the
   Automatic Link button:

   Sub AutomaticLink_Click ()
      Request.Visible = FALSE     'No need for button with automatic link.
      Text1.LinkMode = NONE       'Clear DDE Link.
      Text1.LinkMode = AUTOMATIC  'Reestablish new LinkMode.
   End Sub

7. Add the following code to the Click event procedure of the
   Request button:

   Sub Request_Click ()
      'With a manual DDE link this button will be visible and when

      'selected it will request an update of information from the source
      'application to the destination application.
       Text1.LinkRequest
   End Sub

8. Add the following code to the Click event procedure of the Poke
   button:

   Sub Poke_Click ()
      'With any DDE link this button will be visible and when selected
      'it will poke information from the destination application to the
      'source application.
      Text1.LinkPoke
   End Sub

STEP THREE: Run the Visual Basic Destination Application
--------------------------------------------------------

You have two choices:

 - Run the Visual Basic destination application from the Visual Basic
   environment by skipping to step 4 below.
 - Save the application. Then create an .EXE file, and run it from Windows
   by beginning with step 1 below.

1. From the Visual Basic File menu, choose Save, and save the Form and
   Project naming both DEST.

2. From the File menu, choose Make EXE File. Name it DEST.EXE.

3. Exit from Visual Basic.

4. Run the application from Windows if an .EXE file or from the Visual
   Basic environment.

5. Form1 of the destination application will be loaded and Excel will
   automatically start with the document SOURCE.XLS loaded.

6. Make sure the main title bar in Excel reads "Microsoft Excel," not
   "Microsoft Excel - SOURCE.XLS." If the title bar is incorrect, make
   sure the SOURCE.XLS window is not maximized. To do this choose Arrange

   from the Window menu.

STEP FOUR: Experiment with DDE between Visual Basic and Excel
-------------------------------------------------------------

1. Try typing some text in R1C1 in the spreadsheet. Then click the Request
   button. The text appears in the text box.

   Be sure to press the ENTER key after entering text into an Excel cell
   before clicking the Request button in the Visual Basic program. If you
   don't, a "Timeout while waiting for DDE response" error message will

   display because of the TEXT1.LINKREQUEST statement. This occurs because
   while entering text into a cell, Excel is in a polling loop for data
   entry. No real data is transferred to the cell until you press ENTER.
   Therefore, Visual Basic continues to request the data from the cell, but
   Excel does not pay attention to the request until it exits the polling
   loop, which results in the DDE time-out message.

2. Choose the Automatic Link button and then type some more text in

   R1C1 of the spreadsheet. The text is automatically updated in the
   Visual Basic text box.

3. Type some text in the text box in the Visual Basic application and
   choose the Poke button. The text is sent to R1C1 in the Excel
   spreadsheet.

Note: If you have the Ignore Remote Requests option selected in the
Excel Workspace dialog box, you will not be able to establish DDE from
Visual Basic. Make sure the Ignore Remote Requests option isn't selected.

For Visual Basic version 1.0 add the following constants to the general

declarations of the form:

   CONST TRUE = -1
   CONST FALSE = NOT TRUE

For more information on DDE between Visual Basic and other Windows-based
applications, query on the following words in the Microsoft Knowledge Base:

   DDE and Visual Basic

Additional reference words: 1.00 2.00 3.00 4.00
KBCategory: kbinterop kbprg kbcode
KBSubcategory: IAPDDE

Copyright (c) Microsoft Corporation. All rights reserved.



Wed, 20 Jan 1999 03:00:00 GMT  
 Create Excel-sheet with VB4 ??


Quote:
>I think the best thing is to open Excel within VB and use DDE. The following
>article is from the VB Knowledge Base.

Keep in mind that the DDE-commands are language specific. This also
applies to the internal commands you may use if you create a
Excel-object in Visual Basic. However (I haven't checked this, but I
think it's so) if you *always* use the *English* commands, it may
work. So, if you have a localized version of Excel you will need to
obtain the specs on the international commands.

Hope this helps.

Trond Solberg
----

http://www.mandatum.no/soltro



Sun, 24 Jan 1999 03:00:00 GMT  
 Create Excel-sheet with VB4 ??


Quote:
>Excel-object in Visual Basic. However (I haven't checked this, but I
>think it's so) if you *always* use the *English* commands, it may
>work.

It won't. That is, with Word 6.0, it doesn't work that way. You have to use
the language specific commands. I guess Excel's the same shit.

--
VB Info: http://www.sn.no/~balchen/vb/visual.htm
FAQ: http://www.sn.no/~balchen/vb/faq.htm
Knowledge Base: http://www.sn.no/~balchen/vb/kb.htm



Sun, 24 Jan 1999 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Create Excel-sheet with VB4 ??

2. Somebody please help me to create an Excel Workbook w/sheets from VB4.0

3. Creating charts as objects in excel sheets using vb6 / vb dotnet

4. Creating Excel sheets from VB5

5. Creating and Saving Excel Sheets

6. HOW TO CREATE A DYNAMIC ROWS IN THE TABLE AS IN EXCEL SHEET

7. Creating Excel-sheet with VBS-problem

8. Error creating Access-DB from Excel-sheet??

9. Creating an Excel sheet from VB5 Enterprise

10. HELP NEEDED: Creating Excel charts with VB4-32

11. Excel Sheet Names - Excel.Application.Sheets("sheet1")

12. Close and Save Excel Sheet from Access

 

 
Powered by phpBB® Forum Software