Using the ExecuteExcel4Macro Method

[CarlRostron]

Having all kinds of prblems trying to get this to work.

I want to read a value of a cell from a closed workbook. I have since read that using the ExecuteExcel4Macro method is the best way to approach this (tell me if it isn't).

  Dim ACPRanges As clACPRanges
  Dim arg As String
  Dim Sheet As String
  Dim GetValue As Variant
  Dim ref As String
  Dim theRange As Object
  Dim objExcel As Object
  
  Set ACPRanges = New clACPRanges
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = False
  
  
  For j = 1 To UBound(ACPFilepaths)
    Set f = fs.GetFile(ACPFilepaths(j))
    'sheetName = 1110014_2012_10_25_Task 02 - AC
    Sheet = Left(f.Name, 31)
    ref = ACPRanges.flowPlannedStartDate
    'Set theRange = objExcel.Range(ref).Address(
    'MsgBox Range(ref).Address(True, True, xlR1C1)
    
    [B]'THIS IS WHERE THE CODE FAILS[/B]
    arg = "'" & f.ParentFolder & "\" & "[" & f.Name & "]" & _
          Sheet & "'!" & "R1C1"
    MsgBox (arg)
 
    GetValue = ExecuteExcel4Macro(arg)

Please can you advise on this at all? I have had to put "R1C1" in the string as I couldn't get the Range object to work either.

 

[Rx_]

Look over my post:
http://www.access-programmers.co.uk/forums/showthread.php?t=233117

This was created to open hundreds of Excel files, read a few cells to determine if they were part of a template and if they were to grab all the data from each worksheet.
This eventually allowed me to read hundreds (maybe thousands) of worksheet spread across the network folders and harvest the data into SQL server.

I looked into the old Excel Macro you posted. Tried and failed to set a reference from Access that would work. The remote reference to the Range string is difficult. Spent 30 minutes searching, evidently no other solution was posted either. I posted the question with some code over at Mr. Excel. There is one person there that has helped me on difficult things like this. If I get something useful, will bring it back to here.

 

[CarlRostron]

Thanks for the comments and the extra legwork. I have now done this through an alternative method but would be interested to learn if this could still work.

 

[Rx_]

http://www.mrexcel.com/forum/excel-...ons-module-cant-seem-get-reference-right.html

It is worth noting that using the other methods to silentlly open the Excel and extract data is more efficient.
I played around with the string for some time and could not get it right. The person responding claims I have an extra character at the end. While I typically reserve an extra character at the end for my mystery novels, it is something not acceptable in my code.

That said, I will continue to use the other methods rather than this excel4macro. (A.K.A. "I totally give up!, its beyond my mental ability").
If you do get a solution, please post it for the benefit of others.

 

[Rx_]

This was an interesting response from an advanced Excel programmer (emphasis are mine):
I'd run it against accessing the workbook via ADO - With ADO you don't need a reference to Excel and you don't need to create an instance

That is very interesting to me. Never tried it before out of old habits.