Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Read from an Excel Speadsheet

Posted on 1999-07-22
14
Medium Priority
?
301 Views
Last Modified: 2010-04-30
Hi. I would like you to give me the code which help me to read and write in an Excel file. For example let's say I want to know what is pronted in the A1 box? I would prefer not using OLE. I want very simple code.

Thanks in advance

alex_dagger
0
Comment
Question by:alex_dagger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 1

Expert Comment

by:raygibbins
ID: 1526631
VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "CExcel97"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

' Class       : CExcel97
' Description : Class for working with Microsoft Excel 97 through Automation

Private m_objExcel As Excel.Application
Private m_objWorkbook As Excel.Workbook

Public Property Get AppExcel() As Excel.Application
  ' Returns: A handle to the current instance of Excel
  '
  Set AppExcel = m_objExcel
 
End Property

Public Property Get CurWorkbook() As Excel.Workbook
  ' Returns: A handle to the currently open workbook
 
  Set CurWorkbook = m_objWorkbook
 
End Property

Public Sub CloseExcel()
  ' Comments  : Closes Excel
  ' Parameters: None
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_objExcel.Quit
 
  Set m_objExcel = Nothing
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CloseExcel"
  Resume PROC_EXIT
 
End Sub

Public Sub CloseWorkbook( _
  fSave As Boolean)
  ' Comments  : Closes the current workbook
  ' Parameters: fSave - True to save changes, False to discard changes
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_objWorkbook.Close SaveChanges:=fSave
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CloseWorkbook"
  Resume PROC_EXIT
 
End Sub

Public Sub CreateTableFromAccess( _
  strDatabase As String, _
  strDataSource As String, _
  fFieldNames As Boolean, _
  Optional varMaxRecs As Variant)
  ' Comments  : Gets the contents of an Access table or query into
  '             the current document
  ' Parameters: strDatabse - full path and name of the Access database
  '             you want to read from
  '             strDataSource - name of a table or query in the database
  '             to read records from
  '             fFieldNames - True to put the field names in the first
  '             row, false otherwise.
  '             varMaxRecs - optional: set to the maximum number of
  '             records you want to retrieve. To include all records,
  '             don't specify this argument.
  ' Returns   : Nothing
  '
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim intCounter As Integer
  Dim intFieldCount As Integer
  Dim lngRowCount As Long
  Dim varField As Variant
  Dim intRow As Integer
  Dim intCol As Integer
 
  On Error GoTo PROC_ERR
 
  ' Open the database objects
  Set dbs = DAO.DBEngine.OpenDatabase(strDatabase)
  Set rst = dbs.OpenRecordset(strDataSource)
  intFieldCount = rst.Fields.Count
 
  intRow = 1
  intCol = 1
 
  ' Add the field names if specified
  If fFieldNames Then
    For intCounter = 1 To intFieldCount
      m_objWorkbook.ActiveSheet.Cells(1, intCounter).Value = _
        rst.Fields(intCounter - 1).Name
    Next intCounter
  End If
 
  ' Start inserting data on the second row of the table
  lngRowCount = 2
 
  With rst
    ' Loop through all records
    Do Until .EOF
   
      For intCounter = 1 To intFieldCount
        ' Add each fields value
        varField = .Fields(intCounter - 1).Value
       
        ' Handle null field values
        If IsNull(varField) Then
          varField = "<null>"
        End If
       
        m_objWorkbook.ActiveSheet.Cells(lngRowCount, intCounter).Value = _
          varField
         
      Next intCounter
 
      lngRowCount = lngRowCount + 1
     
      ' See if we are still in range
      If Not IsMissing(varMaxRecs) Then
        If lngRowCount > varMaxRecs Then
          Exit Do
        End If
      End If
     
      ' Move to the next record
      .MoveNext
 
    Loop
  End With
 
  ' Cleanup
  rst.Close
  dbs.Close
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CreateTableFromAccess"
  Resume PROC_EXIT
 
End Sub

Public Sub CreateWorkbook( _
  strName As String, _
  fSave As Boolean)
  ' Comments  : Creates a new workbook and saves it
  ' Parameters: strName - name for the new workbook
  '             fSave - True to save, False to leave unsaved
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  Set m_objWorkbook = m_objExcel.Workbooks.Add

  m_objWorkbook.SaveAs filename:=strName
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "CreateWorkbook"
  Resume PROC_EXIT
 
End Sub

Public Sub InsertValue( _
  strRange As String, _
  varValue As Variant)
  ' Comments  : Inserts values into cells
  ' Parameters: strRange - string defining the range to insert into
  '             varValue - value to insert
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_objWorkbook.ActiveSheet.Range(strRange).Value = varValue
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "InsertValue"
  Resume PROC_EXIT
 
End Sub

Public Sub OpenWorkbook( _
  strFileName As String, _
  fReadOnly As Boolean, _
  Optional varPassword As Variant)
  ' Comments  : Opens the named file and associates it with the class
  ' Parameters: strFileName - full path and name of the file to open
  '             fReadOnly - True to open readonly
  '             varPassword - Optional: specify the password if the
  '             workbook file is password protected.
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  If Not IsMissing(varPassword) Then
    Set m_objWorkbook = m_objExcel.Workbooks.Open( _
      strFileName, _
      , _
      fReadOnly, _
      , _
      varPassword)
  Else
    Set m_objWorkbook = m_objExcel.Workbooks.Open( _
      strFileName, _
      , _
      fReadOnly)
  End If
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "OpenWorkbook"
  Resume PROC_EXIT
 
End Sub

Public Sub OpenWorkbookFromLib( _
  strFileName As String, _
  fReadOnly As Boolean, _
  Optional varPassword As Variant)
  ' Comments  : Opens the named file and associates it with the class.
  '             This version looks in the Excel library folder.
  ' Parameters: strFileName - name of the file to open
  '             fReadOnly - True to open readonly
  '             varPassword - Optional: specify the password if the
  '             workbook file is password protected.
  ' Returns   : Nothing
  '
  Dim strLibPath As String
 
  On Error GoTo PROC_ERR
 
  strLibPath = m_objExcel.LibraryPath & _
    m_objExcel.PathSeparator & _
    strFileName
   
  If Not IsMissing(varPassword) Then
    Set m_objWorkbook = m_objExcel.Workbooks.Open( _
      strLibPath, _
      , _
      fReadOnly, _
      , _
      varPassword)
  Else
    Set m_objWorkbook = m_objExcel.Workbooks.Open( _
      strLibPath, _
      , _
      fReadOnly)
  End If
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "OpenWorkbookFromLib"
  Resume PROC_EXIT
 
End Sub

Public Sub PrintSheet( _
  intFrom As Integer, _
  intTo As Integer, _
  intCopies As Integer, _
  fPreview As Boolean, _
  fPrintToFile As Boolean, _
  fCollate As Boolean)
  ' Comments  : Prints the active workbook
  ' Parameters: intFrom - starting page number
  '             intTo - ending page number
  '             intCopies - number of copies
  '             fPreview - True for print preview
  '             fPrintToFile - True to print to a file. Excel will prompt
  '             for the filename when this is set to True.
  '             fCollate - True to collate copies
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  m_objWorkbook.PrintOut _
    intFrom, _
    intTo, _
    intCopies, _
    fPreview, _
    , _
    fPrintToFile, _
    fCollate
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "PrintSheet"
  Resume PROC_EXIT
   
End Sub

Public Sub SortRange( _
  strRange As String, _
  strKey As String, _
  Optional fAscending As Boolean = False)
  ' Comments  : Sorts the specified range
  ' Parameters: strRange - range to sort
  '             strKey - range to use as the key for sorting
  '             fAscending - True for ascending, False for descending
  ' Returns   : Nothing
  '
  Dim lngSort As Integer
 
  If fAscending Then
    lngSort = xlAscending
  Else
    lngSort = xlDescending
  End If
 
  m_objWorkbook.ActiveSheet.Range(strRange).Sort _
    Key1:=ActiveSheet.Range(strKey), order1:=lngSort
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "SortRange"
  Resume PROC_EXIT
   
End Sub

Public Sub StartExcel(fVisible As Boolean)
  ' Comments  : Starts an instance of Excel
  ' Parameters: fVisible - True to make Excel visible
  ' Returns   : Nothing
  '
  On Error GoTo PROC_ERR
 
  Set m_objExcel = New Excel.Application
  m_objExcel.Visible = fVisible
 
PROC_EXIT:
  Exit Sub
 
PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "StartExcel"
  Resume PROC_EXIT

End Sub


0
 

Author Comment

by:alex_dagger
ID: 1526632
Thanks but do you call this a simple and easy code? I don't.
As you have already understand, I'm new to VB so please some help wouldn't be harmfull.

Thanks anyway

alex_dagger
0
 

Author Comment

by:alex_dagger
ID: 1526633
Please I really need this code pretty soon. If you have some links which will help me, I'll be glad to hear them.

Thanks

alex_dagger
0
Technology Partners: 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!

 
LVL 5

Expert Comment

by:mayhew
ID: 1526634
Simple code:   :)


Dim app As Excel.Application
Dim strData As String
     
'Create instance of Excel object.
Set app = New Excel.Application
     
'Make app visible so you can see what happens.
app.Application.Visible = True

'Open an existing workbook.
app.Workbooks.Open app.Path & "\" & "MyWorkbook.xls"

'Select a sheet.
app.Application.Sheets("Daily").Select

'Select a cell.
app.Application.Range("A11").Select

'Read the data.
strData = app.Application.ActiveCell.Value

'Or enter some data.
app.Application.ActiveCell.Value = "Whatever you want."

'Save the workbook.
app.Application.Workbooks(1).Save

'Or save as a different workbook.
app.Application.Workbooks(1).SaveAs

'Close the workbook.
app.Application.Workbooks(1).Close

'Close the app.
app.Application.Quit
Set app = Nothing


0
 

Author Comment

by:alex_dagger
ID: 1526635
Thanks. I had already your code from the other question you answered to but it did't worked because I got this message:
Compile Error   User-defined type not defined
and the problem was on the statement Dim app As Excel.Application . I tried different things and found something. I can write and read on a new Excel file which I open with the statement Set obj=CreateObject ("Excel.sheet") but I can't write on a existing file which I open with the statement Set obj=GetObject ("C:\file.xls"). The message which appears is: Application-defined or object-defined error (Run-time error '1004'). This error when my application starts reading in this file.


Thanks



alex_dagger
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1526636
Yes, you need to include a reference to the Microsoft Excel library in Project|References.

If you include that, my code will work.

Let me know if you have a question about it.
0
 

Author Comment

by:alex_dagger
ID: 1526637
You were right. This was missing but I will still reject your answer because off one thing. Here is the code I used to try your answer:

Private Sub Command1_Click()
 Refresh
 Dim app As Excel.Application
Dim strData As String
Set app = New Excel.Application
app.Workbooks.Open "C:\Windows\Desktop\alex.xls"
app.Application.Sheets("Sheet1").Select
app.Application.Range("A1").Select
strData = app.Application.ActiveCell.Value
MsgBox strData
app.Application.ActiveCell.Value = "Hello."
app.Application.Workbooks(1).Save
'Close the workbook.
app.Application.Workbooks(1).Close

'Close the app.
app.Application.Quit
Set app = Nothing
End
End Sub


Before I run the application the data in the A1 cell was "Hi!!". I clicked start and then the command1 button. A message box appeared and telling me "Hi!!!". Then I waited for the application to finish and finally restarted it. The message box showed me the following message: "Hi!!!" instead of "Hello!!!". So when the application finished I opened the alex.xls file with Excel and in the A1 cell I saw "Hello!!!". So what it's happening. Why doesn't the message box show me the new data in the A1 cell as it is suppose to? This is why I added the REFRESH statement.

Thanks and if you have an answer to this insert it in the form of a proposed answer and you will get your points.

Thanks a lot


alex_dagger
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1526638
I'm not sure what the problem there is.

I do know that the extra END statement is going to give you trouble.

You might take that out and try it again.

I could see how it could cause problems to have you program suddenly stop.
0
 

Author Comment

by:alex_dagger
ID: 1526639
I really don't know what happened but I retested the program after I deleted the END statement and everything was just fine. I entered END again in the same place and everything still was working fine. I really don't know what happened. I'm really sorry for rejecting your answer. Repost me a proposed answer so that you (finally) take the points you deserve.

Thanks and sorry again

alex_dagger
0
 
LVL 5

Accepted Solution

by:
mayhew earned 80 total points
ID: 1526640
Hey no trouble.  I'm glad you got it working!  :)
0
 

Author Comment

by:alex_dagger
ID: 1526641
This is it you got your points. I would also you to give me another info. Is there a statement to see the names of all the sheets in a Excel file? I know how to count them but I don't know how to find the names.

Thanks


alex_dagger
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1526642
Yes, you want to iterate through the names using something like:


For intCounter = 1 To app.Application.Sheets.Count

    Debug.Print app.Application.Sheets(intCounter).Name

Next


Hope that helps!  :)
0
 

Author Comment

by:alex_dagger
ID: 1526643
What is Debug.Print
By the way, it doesn't work. Do you have another idea?


alex_dagger
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1526644
Debug.Print prints information to the immediate window which you can see by hitting <CTRL><G>.

You don't have to use that, it's just so you can see something without adding message boxes.

The code I gave you works.  Something must be missing.

Did you declare intCounter as an integer variable?

What kind of error are you getting?
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

661 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