Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 3rd, 2003, 07:48 PM
Registered User
 
Join Date: Jun 2003
Location: San Antonio, tx, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Read and write excel file using VB6

Hello Everyone

Can someone show me how to read and write excel file using VB6.:)


Thanks
sjp
Reply With Quote
  #2 (permalink)  
Old June 3rd, 2003, 09:22 PM
Registered User
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

G'day..

Try this....

================================================== ============
Add a reference to Microsft excel object library:

Option Explicit

Private Sub Command1_Click()
   Dim xlApp As Excel.Application
   Dim wb As Workbook
   Dim ws As Worksheet
   Dim var As Variant

   Set xlApp = New Excel.Application

   Set wb = xlApp.Workbooks.Open("PATH TO YOUR EXCEL FILE")

   Set ws = wb.Worksheets("Sheet1") 'Specify your worksheet name
   var = ws.Range("A1").Value

   'or
   var = ws.Cells(1, 1).Value
   wb.Close

   xlApp.Quit

   Set ws = Nothing
   Set wb = Nothing
   Set xlApp = Nothing



End Sub
Reply With Quote
  #3 (permalink)  
Old June 3rd, 2003, 09:26 PM
Registered User
 
Join Date: Jun 2003
Location: , , USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to tobin0971
Default

sjp,

Here is code to write to a text file and an excel file. As far as the reading from an excel file I am at a loss. You will need to add the Microsoft Common Dialog Control 6.0 (SP3) reference to your project.

As far as using the text file, I do have problems when users enter a comma in a field, it doesn't use text delimiters. It makes it difficult to parse the data when I receive it.

HTH,
Tricia

Call txt(rstSendFile)
Call excel(rstSentFile)


(SEND MODULE)
Option Explicit
Public rstSendFile As ADODB.Recordset
Public objExcel As Object
Public objTemp As Object
Public rstSendInfo As ADODB.Recordset

Public Function txt(rstSendFile As ADODB.Recordset) As Boolean

Dim iTotalRecords As Integer
Dim sFileToExport As String
Dim iFileNum As Integer
Dim msg As String
Dim iIndx As Integer
Dim iNumberOfFields As Integer
Screen.MousePointer = vbDefault

On Error Resume Next
CreateSendFile
If rstSendFile.RecordCount = 0 Then Exit Function

With frmMain.CD1
  .CancelError = True
  .FileName = "Export" & gsCustomerNumber & ".txt"
  .InitDir = App.Path
  .DialogTitle = "Save Text File"
  .Filter = "Export Files (*.txt)|*.txt"
  .DefaultExt = "TXT"
  .Flags = cdlOFNOverwritePrompt Or cdlOFNCreatePrompt
  .ShowSave
End With

'--------------------------------
'-- User cancels the operation --
'--------------------------------
If Err.Number = cdlCancel Then 'operation canceled
   Screen.MousePointer = vbDefault
   msg = "The export operation was canceled." & vbCrLf
   iIndx = MsgBox(msg, vbOKOnly + vbInformation, "Text Export File")
   txt = False
   Exit Function
Else
    On Error GoTo ErrorHandler
End If
'---------------------------------------
'-- Let's save the data now. --
'-- Get the name of the file to save. --
'---------------------------------------
Screen.MousePointer = vbHourglass

iTotalRecords = 0
sFileToExport = frmMain.CD1.FileName
iFileNum = FreeFile()
Open sFileToExport For Output As #iFileNum ' Open file for output.

'-------------------------
'-- Stream out the data --
'-------------------------

iNumberOfFields = rstSendFile.Fields.Count - 1
rstSendFile.MoveFirst
Do Until rstSendFile.EOF
  iTotalRecords = iTotalRecords + 1
  For iIndx = 0 To iNumberOfFields
    If (IsNull(rstSendFile.Fields(iIndx))) Then
       Print #iFileNum, ","; 'simply a comma delimited string
    Else
       If iIndx = iNumberOfFields Then
         Print #iFileNum, Trim$(CStr(rstSendFile.Fields(iIndx)));
       Else
         Print #iFileNum, Trim$(CStr(rstSendFile.Fields(iIndx))); ",";
       End If
    End If
  Next
  Print #iFileNum,
  rstSendFile.MoveNext
  DoEvents
Loop

'----------------
Close #iFileNum
Screen.MousePointer = vbDefault
msg = "Export File " & sFileToExport & vbCrLf
msg = msg & "successfully created." & vbCrLf
msg = msg & iTotalRecords & " records written to disk." & vbCrLf
iIndx = MsgBox(msg, vbOKOnly + vbInformation, "Comma Delimited File")
txt = True

Dim iResponse As Integer
If MsgBox("Do you want to record these records as sent?", _
                        vbYesNoCancel, gsDialogTitle) = vbYes Then
With rstSendInfo
    If .EOF Then
        .MoveFirst
        !FileSentDate = Now()
        .Update
    Else
        !FileSentDate = Now()
        .Update
    End If
End With
End If
Exit Function

ErrorHandler:
    DisplayErrorMessage
    txt = False
End Function

Public Sub CreateSendFile()
Set rstSendFile = New ADODB.Recordset
Set rstSendInfo = New ADODB.Recordset

rstSendFile.CursorLocation = adUseClient
rstSendFile.CursorLocation = adUseClient

rstSendFile.Open "SELECT * FROM qrySendInfo", gcnDue, adOpenForwardOnly, adLockOptimistic, adCmdText
rstSendInfo.Open "select * FROM SendInfo", gcnDue, adOpenForwardOnly, adLockOptimistic, adCmdText
If rstSendFile.RecordCount = 0 Then
    MsgBox "There are no records to export.", vbOKOnly, gsDialogTitle
Else
    MsgBox rstSendFile.RecordCount & " records will be exported.", vbOKOnly, gsDialogTitle
End If
End Sub

Public Sub excel(rstSendFile As ADODB.Recordset)

Dim iIndx As Integer
Dim iRowIndex As Integer
Dim iColIndex As Integer
Dim iRecordCount As Integer
Dim iFieldCount As Integer
Dim sMessage As String
Dim avRows As Variant
Dim excelVersion As Integer

On Error GoTo ErrHandler
CreateSendFile
If rstSendFile.RecordCount = 0 Then Exit Sub

'-- Read all of the records into our array
avRows = rstSendFile.GetRows()

'-- Determine how many fields and records
iRecordCount = UBound(avRows, 2) + 1
iFieldCount = UBound(avRows, 1) + 1

'-- Create reference variable for the spreadsheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

'-- We need this line to insure Excel remains visible if we switch
Set objTemp = objExcel

excelVersion = Val(objExcel.Application.Version)
If (excelVersion >= 8) Then
 Set objExcel = objExcel.ActiveSheet
End If

'-- Place the names of the fields as column headers --
iRowIndex = 1
iColIndex = 1
For iColIndex = 1 To iFieldCount
  With objExcel.Cells(iRowIndex, iColIndex)
     .Value = rstSendFile.Fields(iColIndex - 1).Name
     With .Font
       .Name = "Arial"
       .Bold = True
       .Size = 9
     End With
  End With
Next

'-- memory management --
rstSendFile.Close
Set rstSendFile = Nothing

'-- Just add data --
With objExcel
  For iRowIndex = 2 To iRecordCount + 1
   For iColIndex = 1 To iFieldCount
    .Cells(iRowIndex, iColIndex).Value = avRows(iColIndex - 1, iRowIndex - 2)
   Next
  Next
End With

objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit

Dim iResponse As Integer
If MsgBox("Do you want to record these records as sent?", _
                        vbYesNoCancel, gsDialogTitle) = vbYes Then
With rstSendInfo
    If .EOF Then
        .MoveFirst
        !FileSentDate = Now()
        .Update
    Else
        !FileSentDate = Now()
        .Update
    End If
End With
End If
Exit Sub

Exit Sub
ErrHandler:
    If Err.Number = 429 Then
        MsgBox "You cannot use this feature unless you have Microsoft Excel loaded."
        Exit Sub
    Else
        DisplayErrorMessage
        Exit Sub
    End If
End Sub
Reply With Quote
  #4 (permalink)  
Old June 3rd, 2003, 11:19 PM
n/a
Guest
 
Posts: n/a
Default


You can open an excel spreadsheet with ado and treat each worksheet like an ado recordset

dim oconn as adodb.connection
dim ors as adodb.recordset
'
' this assumes that the first row contains headers
'
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sxlsfilename & ";" & "Extended Properties=""Excel 8.0;HDR=YES;"""
'
' the table name is the worksheet name
'
sTableName = "[sheet1$]"
sTableName = "select * from " & sTableName
'
'Get the recordset
'
Set oRS = New ADODB.Recordset
oRS.Open sTableName, oConn, adOpenStatic, adLockOptimistic
nCols = oRS.Fields.Count

more information can be found on msdn and microsoft knowledge base
Reply With Quote
  #5 (permalink)  
Old June 3rd, 2003, 11:20 PM
Authorized User
 
Join Date: Jun 2003
Location: Lexington, KY, USA.
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

This is just a general comment...

All the MS Office products have an object model you can use if you use the application object that Prometheus mentioned.

The trick is finding the object model documentation, but it's out there. You might search mdsn.microsoft.com, or go to Microsoft's Office web site.

You can open an existing worksheet file and read it, you can create a new one and save it, you can basically do anything that a user in Excel can do.

I have done the same things with Microsoft Project's object model, and it works very well.



-Van
(Old dog learning new tricks...)
Reply With Quote
  #6 (permalink)  
Old June 4th, 2003, 09:26 AM
Registered User
 
Join Date: Jun 2003
Location: San Antonio, tx, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for All your Help.:)
Reply With Quote
  #7 (permalink)  
Old April 23rd, 2004, 04:07 PM
Registered User
 
Join Date: Apr 2004
Location: , , Canada.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi
I just wonder if you can help me to creat a vb6 code to read excel data and read to text file. I dont have a much vb experience and in my project I need to write this code to test my application. If you provide me the sample code, I will be appriciate.
Thanks

Reply With Quote
  #8 (permalink)  
Old September 29th, 2005, 07:57 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi
i just used that code and got an error measege
saying that Excel.Application is not a valid daclaration.

any sugestions?

thanks
asaf

Reply With Quote
  #9 (permalink)  
Old October 29th, 2007, 01:24 PM
Registered User
 
Join Date: Oct 2007
Location: , , Turkey.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

To Prometheus: Really thanks for the answer Prometheus, it solved finally my problem.
To Asaf: If you use Visual Basic 6, then you must add Excel Library to your project (Project > References). But if you use Visual Basic 2005, then you must find another codes, because these codes doesn't work in vb2005.

Reply With Quote
  #10 (permalink)  
Old March 5th, 2008, 12:30 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to bonkbc Send a message via Yahoo to bonkbc
Default

greetings!

Does anyone know the proper syntax to set an excel file's read only/write properties using vb6? Ultimately, I'd like my VB6 form object to be the only part of my program visible to the user. When the user enters DATA into the form and hits "enter" or submit, I am writing it to an excel spreadsheet. The user does not need to be able to see the spreadsheet or have access to manipulate it manually.

I've set oExcel.Visible to false, but once that is done, it seems I am forbidden from writing to the file. Is there a way to have my program write to the file, but keep it hidden behind the scenes (so to speak)?

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to read /write from MPP file using Asp.Net C# amitsrathore General .NET 1 October 25th, 2013 04:08 AM
How to Read,Write, & Save (File Processing) ck C++ Programming 2 April 18th, 2005 08:28 AM
VB6 with Excel write using SQL query tmiller1 Excel VBA 3 March 9th, 2005 01:26 AM
read & write file C# minhpx General .NET 1 February 21st, 2005 04:14 PM
Logging File Access Read & Write ntbluez VBScript 1 November 13th, 2003 12:43 AM



All times are GMT -4. The time now is 12:58 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.