Retrieve Information from Excel Via macro
Retrieve Information from Excel Via macro
(OP)
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.
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
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.
CODE
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
XLApp.Columns("B:B").Select
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
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
XLApp.Columns("B:B").Select
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






RE: Retrieve Information from Excel Via macro
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.
RE: Retrieve Information from Excel Via macro
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
RE: Retrieve Information from Excel Via macro
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.
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
RE: Retrieve Information from Excel Via macro
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!