?
Solved

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

Posted on 2003-03-11
3
Medium Priority
?
449 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

801 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