View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.misc
doctorjones_md doctorjones_md is offline
external usenet poster
 
Posts: 30
Default Links won't update unless files are actually opened

Bret,

I'm working on an "Update Links" issue that sounds similar to what you're
doing. It will effectively open each workbook in a designated path
(allowing them to update) then close them. Here's the macro code I'm
working with:

Put this in a general module of a workbook

sub UpdateAllLinks()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
sPath = "C:\MyDummy\"
sName = Dir(sPath & "*.xls")
do while sName < ""
set bk = Workbook.Open(sPath & sName)
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Unprotect Password:="mypassword"
Next
vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinkSources) Then
For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources)
ActiveWorkbook.UpdateLink _
vLinkSources(iLinkSource), xlExcelLinks
Next
End If
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Protect Password:="mypassword"
Next
bk.Close Savechanges:=True
sName = Dir()
Loop
End Sub

Put a command button on a worksheet in that workbook. Double click on it to
get to the code

Private Sub CommandButton1_click()
UpdateAllLinks
End sub

NOTE: If you're workbooks aren't password protected, you can do away with
the "Unprotect/Protect" portion of the code. I'm still trying to iron-out a
couple of problems, but I'll let you know when I get it to work right.

HTH


"SimonCC" wrote in message
...
I haven't used Crystal Report before so I don't know how exactly the export
process goes. You can try to look for an option where it specifies what
version of Excel to export to. If there is such a thing, hopefully your
version of Excel is on the list. Otherwise you might have to look for
upgrades to your Crystal Report for compatibility to the latest version of
Excel (or up to the Excel version you're using).

An alternate way is to write a macro that opens and saves each of your
many
export files.

-Simon


"BretJacobs" wrote:

Simon,

Believe it or not, simply opening the exported file then saving it
(without
renaming or otherwise manipulating anything) yields the expected result.

It must be something in the export process. I'll try and discover
exactly
what the export options are on the Crystal Export, to see if we can nail
down
the exact problem.

Who knew? Unfortunately there are 52 input files, so that's not an
option
to open them all each time we do an update.

Oh well, thanks for your help!!!

"SimonCC" wrote:

My only guess is that the export file is not the exact proper excel
format
even though it has an .xls extension. How about opening one of the
exported
files, and save it over as type Microsoft Office Excel Workbook with
the same
name. Then close that export file and open your summary report file to
see
if it would update. If that doesn't work I don't really know what else
to
recommend.

-Simon


"BretJacobs" wrote:

The export file is an excel worsksheet (the result of a Crystal
Reports
export to excel), and it has an .xls extension.



"SimonCC" wrote:

The export file from Crystal Reports, is it an Excel file? or is it
like csv
file and such? I think if it's not an Excel file, it wouldn't
recognize the
cell structure until opened.

-Simon


"BretJacobs" wrote:

Yes I have, and to the other reply, yes the Update dialog box
shows when the
file is first opened.

If I try "Edit Links Update Values", with the source files
unopened, all
of the values go to #Ref, and from the "Edit, Links" menu, the
status shows
"Error: Source not found".

Opening the source workbooks then causes all of the references to
update,
automatically.

The source workbooks are Exports from Crystal Reports, could that
have
anything to do with it?

"Michael" wrote:

Brett,

In your linked work book, have you tried

Edit Links Update Values

HTH

Michael


"BretJacobs" wrote in
message
...
Having a tough time working with a linked file. I created a
summary
report
that links to multiple unique separate worksheets. Links
will not
update
(even if all varieties of F9 are tried [shift, ctrl, etc])
unless the
individual files are open also. All files are stored on my
laptop's hard
drive, and are not mapped to any network drive.

I know that is NOT the way the links are supposed to work, as
I have used
links many many times before. However, is there some trick
to Office 2003
that I am missing?