Solved

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

Posted on 2006-12-01
4
210 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
[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
  • 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

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

687 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