Topic: Preventing Update of External References...

[Jim Yadon]

I am having a smal problem with preventing the update of external references that reside with some formulas I am programmatically writing into a XLS type format. I am using this code to access the file...

Code: [Select]

Workbooks.Open Filename:=lblProductPath.Caption, UpdateLinks:=0
' write my data into the cells
' some of it contains formula that reference external objects
Workbooks(jayRtrnFileName(lblProductPath.Caption, "\")).Close SaveChanges:=True

I have a suspicion it's in the writing to the cells I need to focus. The same thing happens when I manually type in the formula. Anyone have a quick way around this? I've been looking through help files for an hour now and I'm stumped.

[Jim Yadon]

Heck, maybe even a way to temporarily bypass Excells normal error system.

 

[Jim Yadon]

So I'm grasping at straws here. I've looked into MSDN and the article I found (the link is at home, I'll update tonight) that MS views this issue as a feature. One that can't be turned off even if you turn off all of the formula error checking and links settings. There 'solution' is to update the Links automattically, update the links in the formula manually or eliminate the formula all together. Phht.

Anyone have time to show me how to capture a system event so I can suash this thing once and for all?

***here's the link I promised***

http://support.microsoft.com/default.aspx?scid=kb;ko;327006

Oh yeah, my favorite quote is now...
Status
Behavior is by design.

[Jim Yadon]

I'm gonna start feeling like Hatch if this keeps up.

So after reading everything I could get my hands on about how Excel handles calculations, I decided it was going to be necessary to try a different tack. No one I came accross has apparently ever had the need to write data into a cell containing an external reference to which they did not have access (this is a file structure issue for the client app). I opted to fool Excel into taking my external reference formula with the following code. It works for Excel but I will not know until tomorrow if the client app that the data I am manipulating will accept this end run.

Here's the code -

Code: [Select]

Sub ExtRefTest1()
  Dim myFormula As Variant
  myFormula = "=xreference1!ireference"
  Range("C2").Select
      Selection.Formula = myFormula
End Sub

Sub ExtRefTest2()
  Dim myFormula As Variant
  myFormula = "=xreference2!ireference"
  Range("C3").Select
      Selection.NumberFormat = "@" 'changes it to a text format
      Selection.Formula = myFormula
End Sub

Sub ExtRefTest3()
  Dim myFormula As Variant
  myFormula = "=xreference3!ireference"
  Range("C4").Select
      Selection.NumberFormat = "@" 'changes it to a text format
      Selection.Formula = myFormula
      Selection.NumberFormat = "General" 'changes it back to a general format
End Sub

Sub GetCellFormats()
  Debug.Print "C2: " & Worksheets("Sheet1").Range("c2").NumberFormatLocal
  Debug.Print "C3: " & Worksheets("Sheet1").Range("c3").NumberFormatLocal
  Debug.Print "C4: " & Worksheets("Sheet1").Range("c4").NumberFormatLocal
End Sub

Test 1 demonstrates the initial problem. Test 2 & 3 work for Excel but I do not know if it works for the client app.

[Jim Yadon]

  Well, it doesn't seem to have worked in the client app. I'm gonna do some more testing later and figure out what's up. Syntax for my data going in may have been wrong. In the mean time, here's an article I found that summarizes how Excel calculates.

http://www.decisionmodels.com/calcsecretse.htm

[Jim Yadon]

So I did some more digging. It seems that there just isn't a way to capture it the way I was accessing the file. After jumping into an Excel forum, it was suggested that I use ADO to access the data in the files. Here's the connection I came up with.

Reference: Microsoft ActiveX Data Objects 2.8 Library (an earlier one may work but this is the one I use)

Code: [Select]

Sub jayADOtest()
  Dim strFilePath As String
  Dim cn As ADODB.Connection

  strFilePath = jayFetchIt
  Debug.Print strFilePath
  Set cn = New ADODB.Connection
  With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strFilePath & ";Extended Properties=Excel 8.0;"
    .Open
  End With
  Debug.Print "Opened"
  cn.Close
  Debug.Print "Closed"
End Sub
... and you may or may not need this little helper in the mix -

Code: [Select]

Function jayFetchIt() As String
  jayFetchIt = Application.GetOpenFilename("[enter a brief desc of your file type here] (*.[extension here]),*.[extension here]", 1, "Select A File", , False)
  If jayFetchIt = "False" Then
    MsgBox "A file has not been selected.", vbCritical, "Oops!"
    jayFetchIt = "A file has not been selected."
  End If
End Function
From there I am familiar enough with SQL that I can get to where I need to be.

Happy coding to you!