Go Premium for a chance to win a PS4. Enter to Win

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

Creating and populating a spreadsheet from VB

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 ?



Thanks
0
AntBon
Asked:
AntBon
  • 2
  • 2
  • 2
  • +1
1 Solution
 
gencrossCommented:
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

'Header
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.
0
 
AntBonAuthor Commented:
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
0
 
gencrossCommented:
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.
0
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!

 
wensinkgCommented:
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
0
 
RodStephensCommented:
wensinkg has a great solution. If you want to mess with the cells "manually," take a look at this example:

    http://www.vb-helper.com/howto_csv_to_excel.html

Rod
0
 
AntBonAuthor Commented:
Thanks, that did the trick
0
 
wensinkgCommented:
YVW
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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