[RESOLVED] Read Excel File via VB6 without Office Application

[lmha]

Hi,

Is there any alternative way to read a .XLS file even though you dont have Microsoft Office installed on machine?

If yes, how?

Thanks.

[Hack]

In order to read an exel file you have to use Excel.

You don't have to have Microsoft Office, but you do have to have that one product.

[si_the_geek]

It depends on how the data is arranged in the file... if it is columns (with no header, or just a one row header) you can read it as if it is a database - but note that it is a bit unreliable (you might not be able to read certain cells), because an Excel file is not a database.

For any other layout it didn't used to be possible, but it actually is these days because Microsoft have released the file format specification for .XLS files... however that could easily take thousands of hours of work, unless you can find pre-made code for it.


What would be far easier than either of those methods is to use .CSV files (which Excel can read/write) rather than .XLS files, because .CSV files are very easy to work with.

[lmha]

Hi, me again..

i hav found an alternative, used ADO instead, i hav found this on one of the posts too.
below is the sample, i made it simple so as not to complicate what i want to stress out.

Assume that this is my excel file:

Col1 Col2
xxx aaa
yyy bbb
zzz ccc

Col1 & Col2 are my header respectively, lets also assume that the name of my sheet is "ProcDate"..
If i use below code, strTable variable will show me "xxx" but if i change the tblCount = 1 or any other number, i will hit an error.. i only want to know how to manipulate fields, know the cell contents, sheets and so on.

i am limited with resource, cant use office, nor excel even viewer.. so must use alternative.

Public Function Read_Excel _
            (ByVal sFile _
            As String) As ADODB.Recordset

sFile = "D:\sample.XLS"


On Error GoTo fix_err
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sconn As String
Dim tblCnt, tblFields As Integer
Dim strTble As String

sFile = "D:\Appbase\TPR\DEV\download\AFSR2W.XLS"

rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.LockType = adLockBatchOptimistic
sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile
rs.Open "SELECT * FROM [ProcDate$]", sconn

tblCount = rs.RecordCount
tblFields = rs.Fields.Count


tblCount = 0
strTble = rs.Fields(tblocount).Value

Set Read_Excel = rs
Set rs = Nothing
Exit Function
fix_err:
Debug.Print Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
End Function

It depends on how the data is arranged in the file... if it is columns (with no header, or just a one row header) you can read it as if it is a database - but note that it is a bit unreliable (you might not be able to read certain cells), because an Excel file is not a database.

For any other layout it didn't used to be possible, but it actually is these days because Microsoft have released the file format specification for .XLS files... however that could easily take thousands of hours of work, unless you can find pre-made code for it.


What would be far easier than either of those methods is to use .CSV files (which Excel can read/write) rather than .XLS files, because .CSV files are very easy to work with.

[si_the_geek]

After the rs.Open line you can read all of the values like this:

Dim lngField as Long
  Do While Not rs.EOF
    For lngField = 0 To rs.Fields.Count -1
      MsgBox rs.Fields(lngField).Value
    Next lngField
    rs.MoveNext
  Loop

In terms of writing to the sheet, you might be able to do this to edit the current row:

  rs.Fields(0).Value = "hello"
  rs.Fields(1).Value = "there"
  rs.Update

...and add a new row by doing the same with rs.AddNew just before it.

I have no idea how (or even if) you can detect the sheet names. One possibility is this:

rs.Open "SELECT * FROM InformationSchema.Tables", sconn

[lmha]

Hi,
Yeah, this is working, now i just have to think how to play & do the trick.

As for detecting the sheets, i will be doing this in order, so i can just set the RS accordingly..

u save me again, You are brilliant. Continue to shower your knowledge.

More power!

After the rs.Open line you can read all of the values like this:

Dim lngField as Long
  Do While Not rs.EOF
    For lngField = 0 To rs.Fields.Count -1
      MsgBox rs.Fields(lngField).Value
    Next lngField
    rs.MoveNext
  Loop

In terms of writing to the sheet, you might be able to do this to edit the current row:

  rs.Fields(0).Value = "hello"
  rs.Fields(1).Value = "there"
  rs.Update

...and add a new row by doing the same with rs.AddNew just before it.

I have no idea how (or even if) you can detect the sheet names. One possibility is this:

rs.Open "SELECT * FROM InformationSchema.Tables", sconn

[lmha]

I have used below approach.

where
DIV$ = Sheet Name
rs.Fields("ACCOUNT NO").Value = Header

sample xls file:
ACCOUNT NO SEQUENCE
XXXXX 1
YYYYY 2
ZZZZZ 3

Public Function insertTTPRAFSR2W(ls_FileCode As String, ls_acctype As String, _
ls_TabName As String, ls_datfile As String) As ADODB.Recordset

On Error GoTo Fix_Err


Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sconn As String
Dim tblCnt, tblFields As Integer
Dim ls_sql As String
Dim ProcDate As String
Dim Debit As String
Dim Credit As String
Dim DRCR As String
Dim Amount As String
Dim AcNo As String
Dim Remark As String
Dim BankInfo As String
Dim Desc As String
Dim ChequeNo As String
Dim FundCode As String

rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.LockType = adLockBatchOptimistic
sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & ls_datfile



rs.Open "SELECT * FROM [DIV$]", sconn
  Do While Not rs.EOF
        If IsNull(rs.Fields("ACCOUNT NO").Value) = True Then
            Exit Do
        ElseIf rs.Fields("ACCOUNT NO").Value = "" Then
            Exit Do
        End If
        
        ProcDate = Trim(rs.Fields("PROCESS DATE").Value)
        BankInfo = IIf(IsNull(rs.Fields("BANK INFO").Value), "", rs.Fields("BANK INFO").Value)
        Desc = IIf(IsNull(rs.Fields("DESCRIPTION").Value), "", rs.Fields("DESCRIPTION").Value)
        Debit = Format(rs.Fields("DEBIT").Value, "#############0.00")
        Credit = Format(rs.Fields("CREDIT").Value, "#############0.00")
        DRCR = IIf(IsNull(rs.Fields("DR/CR").Value), "", rs.Fields("DR/CR").Value)
        Amount = Format(rs.Fields("AMOUNT").Value, "#############0.00")
        AcNo = Trim(Replace((rs.Fields("ACCOUNT NO").Value), "-", ""))
        Remark = IIf(IsNull(rs.Fields("REMARK").Value), "", rs.Fields("REMARK").Value)
        ChequeNo = IIf(IsNull(rs.Fields("CHEQUE NO").Value), "", rs.Fields("CHEQUE NO").Value)
        FundCode = IIf(IsNull(rs.Fields("FUND CODE").Value), "", rs.Fields("FUND CODE").Value)
        
        
        ls_sql = "INSERT INTO " & ls_TabName & ""
        ls_sql = ls_sql & "([AcType],[ProcDate],[BankInfo],[Description],"
        ls_sql = ls_sql & "[Debit],[Credit],[DRCR],[Amount],[AcNo],"
        ls_sql = ls_sql & "[Remark],[ChequeNo],[FundCode])"
        ls_sql = ls_sql & " values ("
        ls_sql = ls_sql & "'DIVFORCLIENT','" & ProcDate & "', '" & BankInfo & "' , '" & Desc & "',"
        ls_sql = ls_sql & "" & Debit & ", " & Credit & ", '" & DRCR & "',"
        ls_sql = ls_sql & "" & Amount & ", '" & AcNo & "', '" & Remark & "',"
        ls_sql = ls_sql & "'" & ChequeNo & "', '" & FundCode & "')"

        ExecuteIUSQL (ls_sql)
    rs.MoveNext
  Loop

Set insertTTPRAFSR2W = rs
rs.Close

* Posting for the sake of future researchers looking for answer..
credits to si_the_geek.

[si_the_geek]

Thanks for sharing, I'm sure somebody will find it useful.


Note that the lines like the following (assuming the field/variable are Strings) can be made much shorter:

        BankInfo = IIf(IsNull(rs.Fields("BANK INFO").Value), "", rs.Fields("BANK INFO").Value)

..because when you append an empty string to a Null you end up with an empty string, and appending an empty string to a string makes no difference to it. So the line can be rewritten like this:

        BankInfo = rs.Fields("BANK INFO").Value & ""

[lmha]

yeah it make sense..


Thanks thanks!

Thanks for sharing, I'm sure somebody will find it useful.


Note that the lines like the following (assuming the field/variable are Strings) can be made much shorter:

        BankInfo = IIf(IsNull(rs.Fields("BANK INFO").Value), "", rs.Fields("BANK INFO").Value)

..because when you append an empty string to a Null you end up with an empty string, and appending an empty string to a string makes no difference to it. So the line can be rewritten like this:

        BankInfo = rs.Fields("BANK INFO").Value & ""