?
Solved

How to create new excel document by using VB 6.0

Posted on 2003-03-18
9
Medium Priority
?
178 Views
Last Modified: 2010-05-01
Hi,

how can i create an exceldocument by simply clicking on a commandbutton? I want the information of several textboxes put nicely in a row in excel. For example:

Private Sub Command1_Click()

 Text3.Text = Val(Text1.Text) + Val(Text2.Text)

 'now i want to open a excel document and put the answer in a cell.
 'by clicking again the next answer must be placed in the next cell.
End Sub

I hope you unsterstand my probable easy question..

thanks!

   
0
Comment
Question by:soontjes1
[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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 3

Expert Comment

by:DocM
ID: 8158376

Hy,

Dim xlApp As Excel.Application
Dim intLineNumber As Long
 
Sub Command1_Click()

   Set xlApp = CreateObject("Excel.Application")
 
   xlApp.Workbooks.Add
 
End Sub

Sub Command2_Click()
 
  Text3.Text = Val(Text1.Text) + Val(Text2.Text)

  intLineNumber = intLineNumber + 1
  'xlApp.Visible = True
  xlApp.Sheets(1).Cells(intLineNumber, 1).Select
 
  xlApp.ActiveCell.Value = Text3.Text
 End Sub
 
Sub Command3_Click()

  xlApp.Quit
  Set xlApp = Nothing

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
 
 On Error Resume Next
 xlApp.Quit
 Set xlApp = Nothing
 
End Sub
0
 

Accepted Solution

by:
itzme earned 120 total points
ID: 8158408
You need to include the microsoft excel reference first and then in the add the following code to get open the excel workbook and add the number in text 3:

Option Explicit
Private m_rowNo As Integer
Private xApp As New Excel.Application
Private xWB As New Excel.Workbook
Private xWS As New Excel.Worksheet
Private Sub Command1_Click()
     
    'Show the excel sheet
    xApp.Visible = True
    m_rowNo = m_rowNo + 1
   xWS.Cells(m_rowNo, 1) = Text3.Text
   
    'To Quit the excel app do the following
    'xApp.Quit
    'Set xApp = Nothing

End Sub

Private Sub Form_Load()
Set xApp = CreateObject("Excel.Application")
Set xWB = xApp.Workbooks.Add

 Set xWS = xWB.Sheets("Sheet1")
End Sub

0
 

Expert Comment

by:itzme
ID: 8158432
You need to include the microsoft excel reference first and then in the add the following code to get open the excel workbook and add the number in text 3:

Option Explicit
Private m_rowNo As Integer
Private xApp As New Excel.Application
Private xWB As New Excel.Workbook
Private xWS As New Excel.Worksheet
Private Sub Command1_Click()
     
    'Show the excel sheet
    xApp.Visible = True
    m_rowNo = m_rowNo + 1
   xWS.Cells(m_rowNo, 1) = Text3.Text
   
    'To Quit the excel app do the following
    'xApp.Quit
    'Set xApp = Nothing

End Sub

Private Sub Form_Load()
Set xApp = CreateObject("Excel.Application")
Set xWB = xApp.Workbooks.Add

 Set xWS = xWB.Sheets("Sheet1")
End Sub

0
Technology Partners: 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!

 

Author Comment

by:soontjes1
ID: 8158652
hi guys,

i'm working whit VB enterprise edition and these following declarations are unkown or (I can't find them).....

Excel.Application
Excel.Workbook
Excel.Worksheet


thanks!
0
 

Expert Comment

by:rossc007
ID: 8158712
Hi,

I assumen you have Microsoft Offie installed? If so the goto project references and click on Microsoft Excel. If not your sort of screwed.

Regards
Ross
0
 
LVL 3

Expert Comment

by:DocM
ID: 8158714
Go to the menu Project/References
and select Microsoft Excel x.x Object Library.
0
 

Author Comment

by:soontjes1
ID: 8159002
thank guys it is working great, but one think left: i've several textboxes and all the answers that are shown in VB i want to put in the same Excel file. Can i assign cellnumbers to them?


thanks!!!!

0
 

Expert Comment

by:itzme
ID: 8159087
For each textbox put it in a different column so when you click the command button change add them to different cells i.e

xWS.Cells(m_rowNo, 1) = Text1.Text
xWS.Cells(m_rowNo, 2) = Text2.Text
xWS.Cells(m_rowNo, 3) = Text3.Text
xWS.Cells(m_rowNo, 4) = Text4.Text

and so on. Is that what you want?
0
 

Author Comment

by:soontjes1
ID: 8166104
Ok now i know how that works but in reality i must get the information out of a listbox. For example i've 3 answers in a lisbox, then i want the three answers also in Excel in three seperate cells.

Bye and thank again. This was my last question.. So the one with an correct answer gets the points!
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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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 Month11 days, 16 hours left to enroll

752 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