VB to Excel Command Error


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: VB to Excel Command Error

  1. #1
    Join Date
    Jun 2006
    Posts
    69

    VB to Excel Command Error

    I have been reading up on Excel macros and having a good bit of trouble converting them to VB.

    For example this command in excel will sort the whole file for you. This is cut from an excel macro.
    Cells.Sort Key1:=Range("A1")
    Error is "Compile Error, Sub or Function not defined"

    Am I wrong in thinking that this function can be called from VB?

    Aside from that could someone help me understand a little more what the Set function does when regarding VB loading Excel files?

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    Not sure if you mean by VB (VBA) which is built in to MS Office Products
    Verses VB as in VB6 or VB.Net.
    Excel Macros are VBA Code already. Otherwise you must use Automation
    to both startup a copy of excel as a server app and then create excel objects
    to use like you would if you were actually in excel.
    If I am in Access and I want to do something in Excel then I must Use Automation;
    But if I am in Excel then Excel's objects are already there so I don't need Automation
    because I am already in Excel.
    Cells() and Range() are Excel Objects, Like Workbooks and Worksheets.

  3. #3
    Join Date
    Jun 2006
    Posts
    69
    Yeah I am using VB 6.0, so I guess that quick sort wont work.

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261

    Talking Not a Problem You just need to use Automation

    First you need to add the Microsoft Excel Objects Library to your references.
    This is found Under the Projects Menu, References Item.
    Code:
    Private Function ExcelSortActiveSheet() As Boolean
      On Error GoTo Done
      Dim xlApp As Excel.Application
      Dim xlSheet As Excel.Worksheet
          
      ' Create new hidden instance of Excel.
      Set xlApp = New Excel.Application
      ' Show this instance of Excel during tests.
      'xlApp.Visible = True 'uncomment to make excel visable
      
      'open excel file
      xlApp.Workbooks.Open "c:\Excel Projects\test.xls"
      'get active sheet
      Set xlSheet = xlApp.ActiveSheet
      'apply your sort 
      xlSheet.Cells.Sort Key1:=Range("A1")
      'close the workbook and save the changes
      xlApp.ActiveWorkbook.Close True
    Done:
      On Error Resume Next
      'End the excel application
      xlApp.Quit
      'Don't forget to free up memory and resources
      Set xlSheet = Nothing
      Set xlApp = Nothing
    End Function

  5. #5
    Join Date
    Dec 2006
    Posts
    49
    Hello
    I am stuck with a problem in excel using vb.
    I just can not write a proper code for cancel button.

    i just want when I click the cancel button the dialog sheet disappear leaving the current sheet as it is.

    i have described the problem in more detail here:
    http://forums.devx.com/showthread.ph...023#post481023

    but didn't get any reply.

    Please reply as soon as possible.

    Thanx in advance.
    Tava

Similar Threads

  1. vb to Excel and back to vb
    By bobbyboy in forum VB Classic
    Replies: 2
    Last Post: 10-11-2005, 08:34 AM
  2. Addressing Excel on screen from VB
    By dmb-job in forum VB Classic
    Replies: 0
    Last Post: 06-10-2005, 06:00 PM
  3. calling excel Subtotal from VB
    By adityachs in forum VB Classic
    Replies: 2
    Last Post: 03-31-2005, 07:21 AM
  4. HELP!! Vb and Excel
    By dimpz in forum VB Classic
    Replies: 2
    Last Post: 03-22-2005, 11:28 AM
  5. The day that everything went wrong!!!
    By WHYVB? in forum .NET
    Replies: 0
    Last Post: 10-03-2001, 09:14 AM

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