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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Manipulating Excel files from VB

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
athe
Asked:
athe
  • 7
  • 6
1 Solution
 
DabasCommented:
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
 
atheAuthor Commented:
where do i find  a MS Excel object??
0
 
DabasCommented:
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.

 
atheAuthor Commented:
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
 
DabasCommented:
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
 
atheAuthor Commented:
i think it should work!! thanks!!
i'm going to test it and i'll come back later to give you the points!! ;)
0
 
atheAuthor Commented:
it worked!! one last thing.... if i don't want to save changes in the same file opened, what i have to do?
0
 
DabasCommented:
athe:

Glad it worked!

Try:

xl.DisplayAlerts = False
xl.Quit

Dabas
0
 
atheAuthor Commented:
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
 
DabasCommented:
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
 
DabasCommented:
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
 
atheAuthor Commented:
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
 
DabasCommented:

wb.SaveAs "NewName.xls"
xl.Quit

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now