Reading excel data into another aplication

[Sean Bartleet]

Hi,

I want to import a range of data from excel into Microsoft project using
VBA. As I do not have Project at home I am experimenting with reading excel
data into word.

I am using the GetObject to check if Excel is open, if not it errors and I
open it.
I then use the Excel open dialog to select a file
See code below.
Once the code finishes running Excel does not remain open.
Also when Excel is not open the GetObject function in my Public Sub
OOpenExcel does not error out as expected and never runs the " If apExcel Is
Nothing Then"

I was thinking that the macro should ask the user to open the relevant
spreadsheet and select the relevant range.
If there is an easier way to do this I would appreciate some advice.

Any assistance will be appreciated.

Regards.

Sean



' ------------------------------------------------------------
' Purpose: Opens Spreadsheet
' ------------------------------------------------------------
' Author: Sean Bartleet
' Modules used: fnOpenExcel
' fnGetFileName
' Forms Used: None
' Class Modules used: None
' References used (External DLL files)(Tools, references):
' Microsoft Excel 8.0 object library
' Used by modules: For forms, list modules that use this form
' Revision History:
' ------------------------------------------------------------
'
Option Explicit
Public apExcel As Excel.Application

Sub OOpenSpreadsheet()
Dim wbExcel As Excel.Workbook
Dim sErrorMessage As String
Dim vPathFileName As Variant
Dim sFileName As String
Dim sPathName As String

Debug.Print "================"
Debug.Print "Start OOpenSpreadsheet sub"

vPathFileName = GGetFileName
Debug.Print "File Name = " & vPathFileName

'Check if this file is open, if not, open it
On Error Resume Next
apExcel.Windows("" & vPathFileName & "").Activate
If Err.Number <> 0 Then
Debug.Print "Error activating " & vPathFileName
Err.Clear
Debug.Print "Try to activate " & vPathFileName
Set wbExcel = GetObject(vPathFileName)
End If
' apExcel.Windows("" & sFileName & "").Visible = True
Dim apMSWord As Object
Set apMSWord = GetObject(, "Word.Application")
Debug.Print "End OOpenSpreadsheet sub"
Debug.Print "XXXXXXXXXXXXXXXXXXXXXXXXXX"
End Sub

' ------------------------------------------------------------
' Purpose: Gets a filename using excel file open dialog
' ------------------------------------------------------------
' Author: Sean Bartleet
' Modules used: fnOpenExcel
' Forms Used: None
' Class Modules used: None
' References used (External DLL files)(Tools, references):
' Microsoft Excel 8.0 object library
' Used by OpenSpreadsheet
' ------------------------------------------------------------
'
Function GGetFileName() As String
Debug.Print "GGetFileName Hello"
' Establish an instance of Excel
OOpenExcel
apExcel.Visible = True
GGetFileName = apExcel.GetOpenFilename("Excel files (*.xls), *.xls")
' apExcel.Application.Visible = True
Debug.Print "GGetFileName Goodby"
End Function

' ------------------------------------------------------------
' Purpose: Opens Excel and
' assigns the application (Excel) to an object
' ------------------------------------------------------------
' Author: Sean Bartleet
' Modules used: None
' Forms Used: None
' Class Modules used: None
' References used (External DLL files)(Tools, references):
' Microsoft Excel 8.0 object library
' Used by modules: OpenSpreadsheet
' ------------------------------------------------------------
'
'Public apExcel As Excel.Application
Public Sub OOpenExcel()
Dim ExcelWasRunning As Boolean
Debug.Print "OpenExcel Hello"
' Try assign Excel to an object, if it fails, Excel is not open,
therefore open.
' If it succeeds then you have an Excel object which you can work with.
On Error Resume Next
Set apExcel = GetObject(, "Excel.Application")
If apExcel Is Nothing Then
Debug.Print "apExcel Is Nothing"
Err.Clear
ExcelWasRunning = False
Set apExcel = GetObject("", "Excel.Application")
apExcel.Application.Visible = True
Debug.Print "Excel not running an instance was created "
If Err.Number <> 0 Then
Err.Clear
MsgBox "Error: Could not establish a connection to Excel. " _
& Err.Description
Set apExcel = Nothing
Exit Sub
End If
Else
ExcelWasRunning = True
Debug.Print "Excel was running"
End If
Debug.Print "OpenExcel Godbye"
End Sub

 

[Tom Ogilvy]

If you want excel to stay open, make the application window visible and set

apExcel.UserControl = True

 

[Sean Bartleet]

Thanks Tom.

Sean

If you want excel to stay open, make the application window visible and
set

apExcel.UserControl = True