Read and write excel file using VB6
 

Hello Everyone

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


Thanks
sjp

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

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

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

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...)

Thanks for All your Help.:)

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

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

any sugestions?

thanks
asaf

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.

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)?