• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

creating a Excell worksheet from Vb

Hello
I want to create a new excel worksheet dynamically from within my application.What I need to do is hand in Product name ,barcode  and a quantity field .Also I have to hand in a formula for a quantity total Does any body have a sample which could show me how this is done.
Regards T
0
visualbasic
Asked:
visualbasic
1 Solution
 
PennywisdomCommented:
Try this :

Set ObjExcel = New Excel.Application
    ObjExcel.SheetsInNewWorkbook = 1
    ObjExcel.Workbooks.Add
    'Disable command button to prevent object being recreated
    cmdEngreport.Enabled = False
    ObjExcel.Workbooks.Open (App.Path & "\Book1.xls")

with ObjExcel.Activesheet  
  .Cells(Row, 1).Value = name
  .Cells(Row, 2).Value = barcode
  .Cells(Row, 3).Value = quantity
End With
   
'   Saves report with a new filename
   
    ActiveSheet.PrintOut
    ObjExcel.ActiveSheet.SaveAs (App.Path & "\Book2.xls")
'   Quit Excel
    ObjExcel.Quit
'   Clear our pointer to excel
    Set ObjExcel = Nothing
0
 
corvanderlindenCommented:
Just got started myself
Hope this gets you on the right track

Dim WithEvents vbExcel As Excel.Application

Dim mySheets As Excel.Sheets
Dim mySheet As Excel.Worksheet
   
Set vbExcel = CreateObject "Excel.Application")
'
' add new with template
'
Call vbExcel.Workbooks.Add(Template:="e:\Temp\cor\cor.xlt")
   
vbExcel.Workbooks(1).Activate
   
Set mySheets = vbExcel.ActiveWorkbook.Worksheets
Set mySheet = mySheets.Item("Urlaub")
   
'use named fields (in template)
vbExcel.ActiveWorkbook.Names("Name").RefersToRange.Value = "Name"
   
'fails if user cancels !!!!!!!!!!!!!
vbExcel.ActiveWorkbook.SaveAs "e:\Temp\name.xls"

vbExcel.ActiveWorkbook.Save
   
vbExcel.ActiveWorkbook.Saved = True
vbExcel.ActiveWorkbook.Close   SaveChanges:=False
   
Set mySheets = Nothing
Set mySheet = Nothing
   
vbExcel.Quit
Set vbExcel = Nothing
   
0
 
visualbasicAuthor Commented:
Just one little thing
How do I add a formula say Sum of 5 Cells
Cheers
0
 
EDDYKTCommented:
Range("A6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
0
 
planaskCommented:
The code given is not a complete solution but should give you an idea of how to DIY the rest.


Dim xlApp
Dim xlSheet

'Open an Excel Spreadsheet
Set xlApp = CreateObject("Excel.Application")
'Uncomment next line if you want to see what is going on in your Excel Sheet
'xlApp.Visible = True
xlApp.Workbooks.Add
Set xlSheet = xlApp.ActiveSheet

xlSheet.Name = "My Sheet1"
'Header row
xlSheet.Cells(1, 1) = "Product Name" 'meaning Row=1 Column=1
xlSheet.Cells(1, 2).Value = "Barcode"  'meaning Row=1 Column=2
'............
'............
'Row = 200, Column = 5 that is where your formula can be
xlSheet.Cells(200, 5).FormulaR1C1 = "=Sum(R2C5:R199C5)"

xlSheet.SaveAs "C:\My Sheet.xls"
xlApp.quit
Set xlSheet = Nothing
Set xlApp = Nothing



Good Luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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