Solved

Creating and populating a spreadsheet from VB

Posted on 2002-07-18
7
286 Views
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 ?



Thanks
0
Comment
Question by:AntBon
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 4

Expert Comment

by:gencross
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

'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
 

Author Comment

by:AntBon
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
0
 
LVL 4

Accepted Solution

by:
gencross earned 50 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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:wensinkg
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
0
 
LVL 3

Expert Comment

by:RodStephens
ID: 7163208
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
 

Author Comment

by:AntBon
ID: 7169950
Thanks, that did the trick
0
 

Expert Comment

by:wensinkg
ID: 7170002
YVW
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS expression Issue finding a string 10 65
How to debug this code 7 56
Getting warning: You are about to delete 1 row(s) 9 43
VB 6 error 5 in windows 10 but not in XP 7 40
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…

914 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

18 Experts available now in Live!

Get 1:1 Help Now