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: 179
  • Last Modified:

How to create new excel document by using VB 6.0

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
soontjes1
Asked:
soontjes1
  • 3
  • 3
  • 2
  • +1
1 Solution
 
DocMCommented:

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
 
itzmeCommented:
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
 
itzmeCommented:
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!

 
soontjes1Author Commented:
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
 
rossc007Commented:
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
 
DocMCommented:
Go to the menu Project/References
and select Microsoft Excel x.x Object Library.
0
 
soontjes1Author Commented:
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
 
itzmeCommented:
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
 
soontjes1Author Commented:
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

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.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now