Improve company productivity with a Business Account.Sign Up

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

looking for example to create an excel file .xls from vb program

I can write a vb program to create a comma delimited output file.   say 10 column, selected by either
comma delimited or tab delimited output file .txt

But instead for my user get confused with few steps to load comma or tab delimited txt file, then save as
xls file.  I am looking for a way to 'direct' output my file into xls worksheet.

Someone told me I can write a template spreadsheet with a maccro to import the comma/tabl delimted
data.   No formula in my output.  I am looking for someone to give me some example , or even suggest
a better way to do this.

I certainly can make the convert comma delimeers txt file to import to xsl in a batch file, and my VB program can 'spawn' a processor to execute.

Thanks

mshox1
0
mshox1
Asked:
mshox1
  • 2
1 Solution
 
sirbountyCommented:
http://www.freevbcode.com/ShowCode.Asp?ID=329 provides a good example.

Dim ApExcel As Object 'To open Excel

Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel
ApExcel.Workbooks.Add 'Adds a new book.
ApExcel.cells(1, 1).Formula = "HELLO"     'Add Text to a Cell
'You can use the line above, changing coordenates to go to any
'cell and you can also add Formulas
ApExcel.Range("A1:Z1").BORDERS.Color = RGB(0, 0, 0) 'Use it to
'change the borders.
ApExcel.Columns("A:AY").EntireColumn.AutoFit 'To adjust the
'column's width.
ApExcel.Range("A:Z").Select 'To establish a selection
ApExcel.Selection.NumberFormat = "0" 'Adding different formats


You can setup a counter to increment the rows (intRow) - and just add one to it each time you need to add a new row.
If you need further assistance, perhaps post your current method and we can easily convert it...
0
 
jmundsackCommented:
Another approach would be to use ADO.

Assume you have a workbook with Sheet1, and the first row of Sheet1 contains field names (one field name per column in Excel).

'open the Excel file via ADO
    Dim objConn As ADODB.Connection
    Dim objRec As ADODB.Recordset
    Set objConn = New ADODB.Connection
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=pathofxlsfile;Extended Properties=""Excel 8.0;"""
    Set objRec = New ADODB.Recordset
    objRec.Open "SELECT * FROM [Sheet1$]", objConn, adOpenKeyset, adLockOptimistic

'now you can use ADO's AddNew and Update to add rows to Excel
    objRec.AddNew
    objRec.Fields(0).Value = 123
    ' etc.
    objRec.Update

'don't forget to clean up
    objRec.Close
    objConn.Close
    Set objRec = Nothing
    Set objConn = Nothing

HTH-Jon
0
 
mshox1Author Commented:
First thank you for both of your input that offer 2 different approach.
Just wondering if I can ask more complete info so I can complete understand the 2 approach:

1. On the first approach, I like it, it sounds pretty straight forward. just use (x,y) locations and
   assign the value to each row and column with value.  But I need little more to finish this up. i.e.
    a. how to close and clean the objective, and save the file to a specific filename (i.e. rpt_061204.xls)
    b. If you can provide me some more complete method, I will appreciate.  
    c. do I have to set reference of Project at VB6 to Micosoft excel 11.0 object library?

2. On the 2nd approach. I also like it, it seems can do one row(0) at a time, add another row. (ado)
   if you can give me example of how to move from one row to another row. it will be very help.
   also, show me how to define and control the output file to be set up.  Because every time, I create
   the excel file, I need to use different filename.

thanks in advance to ask more information.

mshox1
0
 
sirbountyCommented:
No reference needed.


Dim ApExcel As Object 'To open Excel

Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel
ApExcel.Workbooks.Add 'Adds a new book.
ApExcel.cells(1, 1).Formula = "HELLO"     'Add Text to a Cell
'You can use the line above, changing coordenates to go to any
'cell and you can also add Formulas
ApExcel.Range("A1:Z1").BORDERS.Color = RGB(0, 0, 0) 'Use it to
'change the borders.
ApExcel.Columns("A:AY").EntireColumn.AutoFit 'To adjust the
'column's width.
ApExcel.Range("A:Z").Select 'To establish a selection
ApExcel.Selection.NumberFormat = "0" 'Adding different formats
'Save the workbook
ApExcel.ActiveWorkbook.SaveAs ("C:\MyTest.xls")
ApExcel.Quit

Set ApExcel = Nothing
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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