Retrieve Information from Excel Via macro 1

[ShaggyPE]

First off, I want to thank everyone who helped me solve my earlier macro problem. Now, I have a new one. This macro will be part of a larger macro once I get the functionality down.

The goal here is to run the macro in solidworks, it will open a specific excel spreadsheet located in the same directory as the macro file. Within that spreadsheet is some specific text. The macro will find that text and return the value of the cell two to the right from the cell where it found
the original value.

I initially created a macro in Excel by recording it while I did the find operation. I then modified the code to do the offset operation. That macro runs beautifully within excel.

When I try to incorporate that code into the SW macro, all things go awry. I believe I have used the GetObject function properly, but it seems like the macro is getting stuck in some of the excel functionality. See the bolded code. When I run the macro I get an error stating: Run-time error '438': Object doesn't support this property or method.

This leads me to believe that either I can't perform some api operations outside of excel that I can perform within (doubtful), or I am not completely in excel yet (maybe), or I suck (likely). The errored code is in bold below.

Option Explicit

Dim swApp As Object
Dim Part As Object
Dim boolstatus As Boolean
Dim longstatus As Long
Dim FileSys   As Object
Dim Source    As String
Dim AttValue  As String


Private Sub ReadProperties()

Dim XLApp As Object
Set XLApp = GetObject(Source)

'XLApp.Visible = True

  XLApp.Worksheets("sheet1").Activate
    [b]XLApp.Columns("B:B").Select[/b]
    XLApp.Selection.Find(What:="1001001", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    XLApp.ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Activate
    AttValue = XLApp.ActiveCell.Value


End Sub


Private Sub SetDefaults()
  MsgBox "Source file not found. Using macro default."
  '
  ' Add routines here for setting defaults.
  '
End Sub

Sub Main()

  Set swApp = CreateObject("SldWorks.Application")

  Source = swApp.GetCurrentMacroPathName             ' Get macro Path FileName
  Source = Left$(Source, Len(Source) - 26) + "SOLIDWORKS.xls"    ' Set Source FileName
  MsgBox (Source)
  Set FileSys = CreateObject("Scripting.FileSystemObject")
  If FileSys.FileExists(Source) Then                 ' Does source file exist?
    ReadProperties                                   ' Yes, read file
  Else
    SetDefaults                                      ' No, use macro defaults
  End If
  '
  '
  MsgBox (AttValue)

End Sub

If anybody has any sample code that grabs data from excel, I would love to see it. I did some searching and the only mention I found was TheTicks xyz points macro, but that doesn't seem available for download anywhere (I checked his link). The macro I have started is based on Lenny Kikstra's SourceFileAccess macro, but that is intended to grab from a text file. I also sampled from his AssemblyBOM macro, but that one creates an excel file using the createobject function, I believe I need to use the getobject function.

Any help is appreciated.
-Shaggy

 

[handleman]

Here is the code you need to get Excel and open your spreadsheet:

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open Source

I think you may have some confusion on GetObject vs. CreateObject. Unless you're trying to get control of an existing Excel session with SW, you want to use CreateObject. That will create an instance of Excel (not create a new spreadsheet) for you to then control.

 

[ShaggyPE]

handleman,
You are a savior. I knew it would be something simple. And my confusion about the finction of GetObject was the problem. Your code allowed me to do just what I needed. A star for you... again.

-Shaggy

 

[ShaggyPE]

handleman,

Couple more hopefully quick questions. The bigger macro that this function is a part of will be opening and closing multiple SW files and grabbing data from this one excel spreadsheet. When I use the recommendations you suggested it opens up multiple instances of excel (i.e. create object). The help within the SW visual basic editor has this bit of info.

Note: Use CreateObject when there is no current instance of the object. If an instance of the object is already running, a new instance is started, and an object of the specified type is created. To use the current instance, or to start the application and have it load a file, use the GetObject function.

It was from this line that I thought the correct call was GetObject. In order to keep from opening multiple instances of Excel, do I use the CreateObject call the first time and then the GetObject each successive time. If so, what is a good method to establish that Excel is already open.

Ideally I want the macro to open Excel, iterate through a bunch of SW files, adding info from the one excel file, then close Excel when it is done.

-Shaggy

 

[handleman]

This is a case where you want to carefully consider your variable scope and program flow. You really want to create the xlApp object just once and then use that same object through the whole macro. You can do this either by:

1. Declaring xlApp outside any function or sub (same place you declare the variable "Source") and then initializing it in your "main()" sub. In that case, every sub or function will be able to "see" it while your code is running.

2. Declare xlApp inside a function (probably your "main()" sub again) and pass it "by reference" to any sub that needs to use it. In that case you would create and initialize xlApp in your main() sub and pass it to the ReadProperties sub. Instead of

Private Sub ReadProperties()

you would have

Private Sub ReadProperties(ByVal myxlApp as Object)

or better yet:

Private Sub ReadProperties(ByVal myxlApp as Excel.Application)

Then in the sub ReadProperties you would refer to the Excel application object by the variable name myxlApp (rather than xlApp as it's defined in main()).

Hope this helps!