Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB for Excel.Application ( Manage Excel files using VB)

Posted on 2003-03-11
3
Medium Priority
?
467 Views
Last Modified: 2008-10-13
Hi,

I have one sample Layout excel file. I want to open that. Create new excel file using sample excel file.
Write data in that excel file. I want add addition Worksheet tabs in that excel. manipulate the formulas and hightlight the result. And most important things I want to create hyperlink to cells reference to another excel file, which i have already created by program.

Memory management : After completion of processing saved worksheet should close and Excel application also close.
When I see in task maanger, Excel.exe should not appear.

Waiting your nice comments.

Ramesh


0
Comment
Question by:ramesh_khade
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
YeaYuh earned 150 total points
ID: 8119035
I can give you some help with what you want to do, but what you are asking is really something you should pay someone to do.

In VB you have to goto Projects->Reference and check the Microsoft Excel x.0 Object Library

Just add a command button to a form and then enter the following code.

Private Sub Command1_Click()
'Setting up variables for Excel
Dim AppExcel As Excel.Application
Dim wBook As workbook
Set AppExcel = CreateObject("Excel.Application")
Set wBook = AppExcel.Workbooks.Add
AppExcel.Visible = True
'Open file
Workbooks.Open FileName:= _
  "C:\path\filename.xls"
'Save it as something Else
ActiveWorkbook.SaveAs FileName:= _
        "C:\path\filename2.xls"_
   FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

'Add additional Sheets

wBook.Sheets.Add

'Enter in a formula
Range("a1").Value = 1
Range("b1").Value = 2
Range("c1").Formula = "=SUM(A1 * B1)"

'Highlight Answer
Range("c1").Select
Selection.Interior.ColorIndex = 36

'Add a hyperlink reference to another file
Range("d1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "C:\path\filename2.xls"
' Can add SubAddress:=SheetName!B4 to reference another sheet in the file opened

Set wBook = Nothing
AppExcel.Quit
Set AppExcel = Nothing
End
End Sub

I hope this example helps with your project, and another helpful tip is if you don't know how to write code in VB to do something in Excel.  Just record a Macro do your action, stop the macro and then view the code.  It is the easiest way to learn.
0
 

Author Comment

by:ramesh_khade
ID: 8126145
hi,
Helped : hyperlink, closing the Excel application.
But not in multiple sheet management.
Any way thanks for help immediately.
0
 
LVL 3

Expert Comment

by:YeaYuh
ID: 8127078
What kind of things did you want to do with the multiple sheet manipulations?  I would be willing to answer it if you gave me an idea what you need.  Do you want to copy sheets from one workbook to another, move data from one sheet to another.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

564 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