New to VB, Trying to call and manipulate Excel Sheet from VB

[SRG]

I am trying to expand on an program/macro with excel. I want to move it out to VB then call Excel and create and manipulate sheets. I need to be able to see if excel is running. I can execute excel. I need to be able to create sheets, place info into cells and format information.

 

[Jonathan]

Sounds like fun (!?)<br>
<br>
You need to add a reference in to Excel's object library (go to references and look for Microsoft Excel x Object Library, where x is the version you have (I know this is how you do it with version 8, don't know about previous).<br>
<br>
Then you can declare object variables as excel objects, for example<br>
<br>
dim exlApplication as New Excel.Application<br>
dim wrkBook1 as Excel.Workbook<br>
<br>
set wrkBook1 = exlApplication.Workbooks.Add<br>
<br>
will declare and open a new excel application, referenced using exlApplication, and create a new workbook in the application, referenced by wrkBook1.<br>
<br>
All of the Excel objects and stuff will appear in the object browser, so it's probably best to just have a play and try and get used to the way it works.<br>
<br>
Hint: The above kind of statements will run excel in the background, invisible to the user. If your program gets past this point and then crashes, the excel application will still be running. I normally kill it using task manager<br>
<br>
Jon

 

[SRG]

Thanks, I am using Excel 97. Anyone know a way to check if it is installed on the system? get the path for excel.exe (Do I even need it?) and check to see if it is already running?

 

[kleo]

I used this to detect a default browser and launch it, so this can be modified to launch Excel:<br>
<br>
These two statements are module level declares:<br>
Dim m_sPath As String<br>
Private Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long<br>
<br>
In the click event, or whatever your kick off event is:<br>
Private Sub cmdExplore_Click()<br>
<br>
Dim lRetVal As Long<br>
Dim sBrowserExec As String<br>
Dim sFileName As String<br>
Dim sFilePart As String<br>
<br>
On Error GoTo cmdExplore_Click_Error<br>
'fill the string with spaces<br>
sBrowserExec = Space(255)<br>
'fill the string with the path and filename <br>
'of the file you want to open<br>
sFileName = m_sPath & m_sIndex<br>
'find the default web browser or in your case<br>
'the version of Excel on the user's system<br>
lRetVal = FindExecutable(sFileName, sFilePart, sBrowserExec)<br>
'remove the null off of the return value<br>
sBrowserExec = sRemoveNull(sBrowserExec)<br>
'launch excel<br>
Shell sBrowserExec & " " & m_sPath & m_sIndex, vbNormalFocus<br>
Exit Sub<br>
<br>
cmdExplore_Click_Error:<br>
MsgBox Err.Description, vbOKOnly<br>
Resume Next<br>
End Sub<br>
<br>
Private Function sRemoveNull(sString As String) As String<br>
<br>
On Error GoTo sRemoveNull_Error<br>
sRemoveNull = Left$(sString, InStr(sString, Chr(0)) - 1)<br>
Exit Function<br>
<br>
sRemoveNull_Error:<br>
sRemoveNull = sString<br>
End Function<br>
<br>
Hope this helps!<br>
<br>
kleo<br>
<br>
kleo9@hotmail.com