ExecuteExcel4Macro

J

JP Ronse

Hi All,

Using Excel 2003.


Code:


Option Explicit
Const ALT_PLANNING_PATH As String = "D:\Data\Watch\"
Const PLANNING_FILE As String = "ROC 2009.xls"

Sub test()
Dim result As Variant
result = GetValue(ALT_PLANNING_PATH, PLANNING_FILE, "Engineers", "A1")
Debug.Print result
End Sub
'''GetValue(ALT_PLANNING_PATH,PLANNING_FILE,"Engineers", A1)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Function: GetValue
'''
''' Comments: Looks up the value in the planning file
'''
'''
'''
''' Date Developer Action
''' -------------------------------------------------------------------------
''' dd/mm/yyyy John Walkenbach Created
'''
Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
If GetValue = 0 Then GetValue = ""
End Function

As I mentioned in the function comments, I have found the code on John's
page, honour to whom it belongs to.

Is there a way I could use this to get a range of cells instead of one
single cell?

With kind regards,

JP
 
J

John Bundy

I havent done a lot with Excel4Macro so i would hate to give the wrong
answer, I do it this way (first add a 'tools'->'references' to Microsoft
ActiveX Data Objects 2.8 Library):

Sub main()

Dim ConString As String
Dim strSQL As String
Dim DBPATH As String
Dim recordset As New ADODB.recordset

DBPATH = "yourfilepath.xls"

ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & DBPATH & ";" & _
"extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [Data$A1:C10]"
'use the $ after the sheet name, this sheet is named "Data"
Set recordset = New ADODB.recordset

Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText) 'this is all one line with the Call above

Call Sheets("Sheet1").Range("A2").CopyFromRecordset(recordset)
'change sheet and range to output here
Set recordset = Nothing
End Sub
 
J

JP Ronse

Hi John,

I'll try your suggestion. Keep you informed.


With kind regards,

JP

John Bundy said:
I havent done a lot with Excel4Macro so i would hate to give the wrong
answer, I do it this way (first add a 'tools'->'references' to Microsoft
ActiveX Data Objects 2.8 Library):

Sub main()

Dim ConString As String
Dim strSQL As String
Dim DBPATH As String
Dim recordset As New ADODB.recordset

DBPATH = "yourfilepath.xls"

ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & DBPATH & ";" & _
"extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [Data$A1:C10]"
'use the $ after the sheet name, this sheet is named "Data"
Set recordset = New ADODB.recordset

Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText) 'this is all one line with the Call above

Call Sheets("Sheet1").Range("A2").CopyFromRecordset(recordset)
'change sheet and range to output here
Set recordset = Nothing
End Sub


--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.


JP Ronse said:
Hi All,

Using Excel 2003.


Code:


Option Explicit
Const ALT_PLANNING_PATH As String = "D:\Data\Watch\"
Const PLANNING_FILE As String = "ROC 2009.xls"

Sub test()
Dim result As Variant
result = GetValue(ALT_PLANNING_PATH, PLANNING_FILE, "Engineers",
"A1")
Debug.Print result
End Sub
'''GetValue(ALT_PLANNING_PATH,PLANNING_FILE,"Engineers", A1)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Function: GetValue
'''
''' Comments: Looks up the value in the planning file
'''
'''
'''
''' Date Developer Action
''' -------------------------------------------------------------------------
''' dd/mm/yyyy John Walkenbach Created
'''
Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
If GetValue = 0 Then GetValue = ""
End Function

As I mentioned in the function comments, I have found the code on John's
page, honour to whom it belongs to.

Is there a way I could use this to get a range of cells instead of one
single cell?

With kind regards,

JP







.
Click to expand...
Click to expand...
 
J

JP Ronse

Hi John,

As promised, I would post some feedback.

Of course, the first essays didn't work. Although you mentioned clearly that
the call statement had to be all on one line, I didn't follow your advise...
When I realised that this was a necessary statement and not only comment, it
worked perfect.

And I was even able to be more specific in the query:

strSQL = "SELECT [Name (as in outlook)] FROM [Engineers$A:G] WHERE Status =
'Active'"

Just one question, why are the headers not in the result?

Anyway, many thanks for your assistance.



By the way, for more info on working with ExecuteEcel4Macro, see John
Walkenbach's page: http://spreadsheetpage.com/ and search for:

A VBA Function To Get A Value From A Closed File

With kind regards,

JP



John Bundy said:
I havent done a lot with Excel4Macro so i would hate to give the wrong
answer, I do it this way (first add a 'tools'->'references' to Microsoft
ActiveX Data Objects 2.8 Library):

Sub main()

Dim ConString As String
Dim strSQL As String
Dim DBPATH As String
Dim recordset As New ADODB.recordset

DBPATH = "yourfilepath.xls"

ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & DBPATH & ";" & _
"extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [Data$A1:C10]"
'use the $ after the sheet name, this sheet is named "Data"
Set recordset = New ADODB.recordset

Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText) 'this is all one line with the Call above

Call Sheets("Sheet1").Range("A2").CopyFromRecordset(recordset)
'change sheet and range to output here
Set recordset = Nothing
End Sub


--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.


JP Ronse said:
Hi All,

Using Excel 2003.


Code:


Option Explicit
Const ALT_PLANNING_PATH As String = "D:\Data\Watch\"
Const PLANNING_FILE As String = "ROC 2009.xls"

Sub test()
Dim result As Variant
result = GetValue(ALT_PLANNING_PATH, PLANNING_FILE, "Engineers",
"A1")
Debug.Print result
End Sub
'''GetValue(ALT_PLANNING_PATH,PLANNING_FILE,"Engineers", A1)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Function: GetValue
'''
''' Comments: Looks up the value in the planning file
'''
'''
'''
''' Date Developer Action
''' -------------------------------------------------------------------------
''' dd/mm/yyyy John Walkenbach Created
'''
Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
If GetValue = 0 Then GetValue = ""
End Function

As I mentioned in the function comments, I have found the code on John's
page, honour to whom it belongs to.

Is there a way I could use this to get a range of cells instead of one
single cell?

With kind regards,

JP







.
Click to expand...
Click to expand...
 
J

John Bundy

Can't really say without seeing your workbook, mine come back. It is probably
something to do with [Name (as in outlook)] or the where statement
--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.


JP Ronse said:
Hi John,

As promised, I would post some feedback.

Of course, the first essays didn't work. Although you mentioned clearly that
the call statement had to be all on one line, I didn't follow your advise...
When I realised that this was a necessary statement and not only comment, it
worked perfect.

And I was even able to be more specific in the query:

strSQL = "SELECT [Name (as in outlook)] FROM [Engineers$A:G] WHERE Status =
'Active'"

Just one question, why are the headers not in the result?

Anyway, many thanks for your assistance.



By the way, for more info on working with ExecuteEcel4Macro, see John
Walkenbach's page: http://spreadsheetpage.com/ and search for:

A VBA Function To Get A Value From A Closed File

With kind regards,

JP



John Bundy said:
I havent done a lot with Excel4Macro so i would hate to give the wrong
answer, I do it this way (first add a 'tools'->'references' to Microsoft
ActiveX Data Objects 2.8 Library):

Sub main()

Dim ConString As String
Dim strSQL As String
Dim DBPATH As String
Dim recordset As New ADODB.recordset

DBPATH = "yourfilepath.xls"

ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & DBPATH & ";" & _
"extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [Data$A1:C10]"
'use the $ after the sheet name, this sheet is named "Data"
Set recordset = New ADODB.recordset

Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText) 'this is all one line with the Call above

Call Sheets("Sheet1").Range("A2").CopyFromRecordset(recordset)
'change sheet and range to output here
Set recordset = Nothing
End Sub


--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.


JP Ronse said:
Hi All,

Using Excel 2003.


Code:


Option Explicit
Const ALT_PLANNING_PATH As String = "D:\Data\Watch\"
Const PLANNING_FILE As String = "ROC 2009.xls"

Sub test()
Dim result As Variant
result = GetValue(ALT_PLANNING_PATH, PLANNING_FILE, "Engineers",
"A1")
Debug.Print result
End Sub
'''GetValue(ALT_PLANNING_PATH,PLANNING_FILE,"Engineers", A1)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Function: GetValue
'''
''' Comments: Looks up the value in the planning file
'''
'''
'''
''' Date Developer Action
''' -------------------------------------------------------------------------
''' dd/mm/yyyy John Walkenbach Created
'''
Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists

If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
If GetValue = 0 Then GetValue = ""
End Function

As I mentioned in the function comments, I have found the code on John's
page, honour to whom it belongs to.

Is there a way I could use this to get a range of cells instead of one
single cell?

With kind regards,

JP







.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ExecuteExcel4Macro Error 2
GetValue from closed workbook 1
Closed workbook 1
Error retrieving data from closed workbook 5
Trouble with types... 1
variant/error Error 2023 5
Weird... type mismatch only half of the time 1
Retrieving cell formatting from closed workbook 0

Top