DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Using VB6 to write to an Excel file.

  1. #1
    Join Date
    Nov 2004
    Posts
    293

    Using VB6 to write to an Excel file.

    I have a VB6 program that I want to be able to create an Excel spreadsheet with but I am getting an error within my dim of some of the items for the Excel portion. The code looks like this:

    'now we want to dim some stuff for the excel portion of the program if excel is going to be required
    Public appEx As Excel.Application
    Public wb As Workbook
    Public ws As Worksheet
    Public myRange As Range
    Public fnameExcel As String
    Public fsoEx As New FileSystemObject
    Public sDate As String
    Public irowNum As Integer
    Public sSheetName As Sheets
    Public RangeSelection As String

    and I am getting the following error:
    "Compiler error: User-defined type not defined"
    on the above line: Public fsoEx As New FileSystemObject

    This is funny because I copyed the code from a program I have where it's working ok.

  2. #2
    Join Date
    Mar 2005
    Posts
    110
    I think you should reference this, "file menu--project--referencr---Microsoft scripting runtime"

  3. #3
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Yes, you need to add the Microsoft Scripting Runtime reference. May I ask if you are creating the Excel file with it or ???
    becuase its easy to use the EOM to create a new Excel file. No secondary references needed other then having Excel installed.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  4. #4
    Join Date
    Nov 2004
    Posts
    293
    adding the "Microsoft scripting runtime" reference did the trick.

    However I'm not familure with creating an Excel file using EOM. Can you show me how to do that?

  5. #5
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    This is just a simple demo that just adds some text into a cell on both sheet1 and sheet2. Didnt really know what I
    should make it do but hopefully you get the idea.

    Code:
    Option Explicit
    'Add a reference to MS Excel xx.0 Object Library
    Private moApp As Excel.Application
    Private moWB As Excel.Workbook
    
    Private Sub Form_Load()
        Set moApp = New Excel.Application
        moApp.Visible = False
    End Sub
    
    Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
        If TypeName(moWB) <> "Nothing" Then
            moWB.Close True, "C:\Text.xls"
        End If
        Set moWB = Nothing
        If TypeName(moApp) <> "Nothing" Then
            moApp.Quit
        End If
        Set moApp = Nothing
    End Sub
    
    Private Sub Command1_Click()
        Set moWB = moApp.Workbooks.Add
        moApp.Visible = True
        moWB.Sheets("Sheet2").Cells(1, 1).Value = "Added from VB6"
        moWB.Sheets("Sheet1").Cells(1, 1).Value = moWB.Sheets("Sheet2").Cells(1, 1).Value
    End Sub
    Last edited by VBOfficeGuru; 04-18-2005 at 02:50 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  6. #6
    Join Date
    Nov 2004
    Posts
    293
    Thanks for the example, I see you are seting the workbook to in-visible at the offset and then back to visible. I tryed that once but had problems when I would open the workbook up later with MS-Excel. it stayed invisible and I had to keep restarting my computer to get it back.

  7. #7
    Join Date
    Nov 2003
    Location
    Alameda, CA
    Posts
    1,737
    dear Mr. Yoda,
    I liked your QueryUnload code, that explains very well how to close objects (even though VB should do that for us, sometimes automation get confused)
    Although your Typename <> "Nothing" left me perpexed. Instead of a string compare (ok, I do not like strings someone here could say I prefer the

    if not myObject is Nothing then

    that is more clear (at least there is no danger of mispelling Nothing in a string, I saw people doing that... or remembering if Nothing if caps or not)
    Any reason you used string compare?

    Marco
    "There are two ways to write error-free programs. Only the third one works."
    Unknown

  8. #8
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    I guess you could just say that I use the string by old habit
    The If moWB Is Nothing Then blah, blah, blah is better.

    Setting the Excel app to Visible = False at the loading of your program will give your app a cleaner look and
    loading process (since you dont have to worry about excel being the App in focus instead of your program).

    I havent had any problems with the visibility, but most people have issues destroying objects and leaving Excel
    running in the background when their app closes.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center