Solved

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

Posted on 2006-12-01
4
203 Views
Last Modified: 2010-04-30
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
Comment
Question by:mshox1
  • 2
4 Comments
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 18058005
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
 
LVL 13

Expert Comment

by:jmundsack
ID: 18067933
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
 

Author Comment

by:mshox1
ID: 18068093
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 18068552
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now