VB to Excel Command Error

[Force]

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?

[Ron Weller]

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.

[Force]

Yeah I am using VB 6.0, so I guess that quick sort wont work.

[Ron Weller]

First you need to add the Microsoft Excel Objects Library to your references.
This is found Under the Projects Menu, References Item.

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

[tava.amitava]

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