Dear Karrisa Crook,
In order to try to understand first you need to download the column
(and sample database) by Doug Steele
http://www.accessmvp.com/DJSteele/Access/AA200507.zip
Read the AA200507.pdf "Access Answers: Excelling automatically"
..I "think" if you change the following lines from my code
MyCuWorkBook = Forms!MainExclude_Form!DefaultKeyword & ".xls"
MyCuWorkSheet = "Results from Space Hound"
To
MyCuWorkBook = Your Workbook Name (sample.xls)
MyCuWorkSheet = Your WorkSheet Name (Sheet1)
The code will run for you as example
I still have many questions to ask, try my code and let us know if it
worked for you
If not someone else will help us
To all gurus:
I am developing the code as "Late Binding" I will like to convert to
"Early Binding"
What do I have to change in my code?
My Entire code:
Sub ReadFromWorkbook()
' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: This routine opens an Excel Workbook using the value of
the
' TextBox: DefaultKeyword from the Form named MainExclude_Form
' in the same folder as the Database, and read a Worksheet
' named Results from Space Hound in that Workbook. If the Worksheet
' exists, the routine makes sure that cell A1 contains
' the words "File Name". If it does, it displays the
' contents of cell A2 in a message box.
'
' Inputs: None
Dim booXLCreated As Boolean
Dim objActiveWkbk As Object
Dim objActiveWksh As Object
Dim objXL As Object
Dim strWorkbookName As String
Dim MyCuWorkBook As String 'My Current Work Book
Dim MyCuWorkSheet As String 'My Current Work Sheet
'Acces 2002 Make sure Microsoft Excel 10.0 Object Library is included
in the 'References
'Acces 2003 Make sure Microsoft Excel 11.0 Object Library is included
in the 'References
On Error GoTo Err_ReadFromWorkbook
'Initialize Variables
MyCuWorkBook = Forms!MainExclude_Form!DefaultKeyword & ".xls"
MyCuWorkSheet = "Results from Space Hound"
'<Drive>:\Location\File.mdb
strWorkbookName = CurrentDb().Name
MsgBox strWorkbookName
'Determine the full path to the desired Workbook
'<Drive>:\Location\MyCuWorkBook
strWorkbookName = Left$(strWorkbookName, _
Len(strWorkbookName) - Len(Dir$(strWorkbookName))) & _
(MyCuWorkBook)
MsgBox strWorkbookName
'Check to make sure the Workbook exists
If Len(Dir(strWorkbookName)) = 0 Then
MsgBox strWorkbookName & "Workbook Not Found"
Else
MsgBox strWorkbookName & "Workbook Found"
'Get a instance of Excel that we can use
'If it's already open, use it.
'Otherwise, create an instance of Excel
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
'An error will be raised if Excel isn't already open.
If Err.Number = 0 Then
booXLCreated = False
MsgBox "Err.Number is Not equal to 0"
Else
Set objXL = CreateObject("Excel.Application")
booXLCreated = True
MsgBox "Err.Number is Diferent to 0"
End If
On Error GoTo Err_ReadFromWorkbook
objXL.Application.Workbooks.Open strWorkbookName
With
objXL.Application.Workbooks(MyCuWorkBook).Worksheets(MyCuWorkSheet)
If .Range("A1") = "File Name" Then
MsgBox "Cell A2 contains " & .Range("H1"), vbOKOnly + vbInformation
Else
MsgBox "Cell A1 does not contain File Name", vbOKOnly + vbCritical
End If
End With
End If
End_ReadFromWorkbook:
On Error Resume Next
objXL.Application.worksbooks(MyCuWorkBook).Close SaveChanges:=False
If booXLCreated Then
objXL.Application.Quit
End If
Set objXL = Nothing
DoCmd.Hourglass False
Exit Sub
Err_ReadFromWorkbook:
MsgBox Err.Number & ": " & Err.Description & " in ReadFromWorkbook", _
vbOKOnly + vbCritical, "I can not read from Workbook"
Resume End_ReadFromWorkbook
End Sub
