Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Creating and populating a spreadsheet from VB

Posted on 2002-07-18
Medium Priority
Last Modified: 2010-05-02
I have a VB program that reads a comma seperated file, and with the values that i have read in and manipulated i wish to automatically create an Excel spreadsheet and insert the values, headers etc. into this spreadsheet

Can someone show me how to do the 'spreadsheet' business ?

Question by:AntBon
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
  • 2
  • 2
  • +1

Expert Comment

ID: 7162714
If you are doing a simple XLS file you can open a file for output and insert the values tab delimited in the file and close it...

Dim iFreeFile As Integer

iFreeFile = FreeFile
Open "c:\testxls.xls" For Output As iFreeFile

sHeaderBuffer = "Cell1" & vbTab & "Cell2" & vbTab & "Cell3"

Print #iFreeFile, sHeaderBuffer
Print #iFreeFile, vbTab

'Cell Data
sHeaderBuffer = "Data1" & vbTab & "Data2" & vbTab & "Data3"

Print #iFreeFile, sHeaderBuffer
Print #iFreeFile, vbTab

Close iFreeFile

Now you can open this file in Excel.  I use this method when I have a very simple spreadsheet to create.  It is extremely fast and easy.  

If you need to do a lot of formatting and things like that you are going to want to use the Excel Object model.

Author Comment

ID: 7162723
Yep i think i need to use the excel object model, becuase i want the user to just click a button and have the the spreadsheet be populated

Accepted Solution

gencross earned 200 total points
ID: 7162763
Here is some code to open the XLS and access individual cells

    Dim xlsApp As Excel.Application
    Dim xlsWB As Excel.Workbook
    Dim xlsWS As Excel.Worksheet
    'Open and Import the XLS
    Set xlsApp = CreateObject("Excel.Application") 'run it
    Set xlsWB = xlsApp.Workbooks.Open("c:\xlsfile.xls")
    Set xlsWS = xlsWB.Sheets(1)
    xlsApp.Cells(1,1) = "SomeText"

There are many options and formatting.  Take a look through the object.
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.


Expert Comment

ID: 7162809
Now this assumes that you have Excel on the machine that you are targeting.  The example takes a comma delimited file (With headers in your case) and saves it to an excel format.

1st, set a reference to the Microsoft Excel Object Library in your project.

Option Explicit
Dim oExcel as Excel.Application

Private Sub Command1_Click()
    Set oExcel = New Excel.Application
    oExcel.Workbooks.Open strFilename, , , xlCSV
    oExcel.Workbooks(0).SaveAs strNewFilename, xlExcel7
End Sub

Expert Comment

ID: 7163208
wensinkg has a great solution. If you want to mess with the cells "manually," take a look at this example:


Author Comment

ID: 7169950
Thanks, that did the trick

Expert Comment

ID: 7170002

Featured Post

Independent Software Vendors: 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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

670 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