?
Solved

Manipulating Excel files from VB

Posted on 2003-02-18
13
Medium Priority
?
226 Views
Last Modified: 2010-05-01
i have an excel file and i have to manipulate it from vb. i need to export report information to excel....

is about OLE?
0
Comment
Question by:athe
[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
  • 7
  • 6
13 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 7978876
Add MS Excel to your Project -> References

Try to view a few examples from help to get ideas on how to use an Excel object in VB

Dabas
0
 

Author Comment

by:athe
ID: 7982278
where do i find  a MS Excel object??
0
 
LVL 27

Expert Comment

by:Dabas
ID: 7997899
First in Project -> References tick the checkbox next to Microsoft Excel Project Library

Then in your project you can type something like:

Dim xl as Excel.Application

Or, press F2 and have a look at the object browser for Excel

Dabas
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:athe
ID: 8058378
i have had a look at the browser for excel but i dont understand how to use it....

i have an excel file and i have to modify it with my program data, how can i manipulate this? where i can learn how to access to the columns and rows?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8059351
Assume you want to fill cells A1, B1, A2, B2 with the numbers 3, 4,5 and 6, this is what the code would look like:

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Public Sub Command1_Click

   Set xl = New Excel.Application
   Set wb = xl.Workbooks.Open(txtFileName) 'Assuming you have a TextBox that holds the file you want to open.
   Set ws = wb.Worksheets(1) 'Point to the first worksheet

   ws.Cells(1, 1) = 1 'A1
   ws.Cells(1, 2) = 2 'A2
   ws.Cells(2, 1) = 3 'B1
   ws.Cells(2, 2) = 4 'B2

End Sub

I hope this can get you started!

Dabas
0
 

Author Comment

by:athe
ID: 8061816
i think it should work!! thanks!!
i'm going to test it and i'll come back later to give you the points!! ;)
0
 

Author Comment

by:athe
ID: 8062205
it worked!! one last thing.... if i don't want to save changes in the same file opened, what i have to do?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8062337
athe:

Glad it worked!

Try:

xl.DisplayAlerts = False
xl.Quit

Dabas
0
 

Author Comment

by:athe
ID: 8062426
but, how do i save changes in another file? the point is to have a pattern file to get the structure, but saving changes in another file.... you know what i mean? is that what you have told me, i think i haven't understood.... :S
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8062510
No, I did not understand you. I thought you wanted to exit Excel without it asking you to save.

What you want gets achieved with:

wb.SaveAs "New File Name"
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8062553
You might be interested in the following link, as it is related to this one

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20537347.html

Dabas
0
 

Author Comment

by:athe
ID: 8067964
when i finish my working with with excel application opened, i close everything but an excel proccess continue execution and i have to finish with CTRL-ALT-SUPR....

i think i need to write some code telling vb to close excel....

i write this code and then, i work with excel file cells....

    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Open(App.Path & "\Envio.xls")
    Set ws = wb.Worksheets(1)

what do i have to write exactly to finish excel and to save in another file (not envios.xls)?
0
 
LVL 27

Accepted Solution

by:
Dabas earned 200 total points
ID: 8067997

wb.SaveAs "NewName.xls"
xl.Quit

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

770 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