Excel Hyperlinks not retained when importing from workbook.

[statum]

Hello all, I have a macro that will import data from a sheet in a
workbook to a sheet on the workbook I am currently working with. The workbook
from which I am copying the data is never opened. However, that workbook has
hyperlinks that need to be copied along with the cell data. The cell data
copies just fine, but the hyperlinks are no longer available. I need a few
lines of code that I can put into the macro that will not just copy the
cell's data values, but also their hyperlinks. Thanks in advance for your
help....

 

[Tim Williams]

How are we to know what lines of code are required if we don't know how
you're importing your data ?

Posting existing code always helps.

Tim

 

[statum]

I am using the standard importing proceedure for importing excel data from
one workbook to another. As I stated in my original post, all the data
imports with no problem. Even the cell data that were assigned a hyperlink
copies without issue. Only the actual hyperlinks are not copied over.

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=" & Filename & ";M" _
, _
"ode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk " _
, _
"Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OL" _
, _
"EDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("WORKLOG$A1:AA10000")
.Name = Filename
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "Filename"
.Refresh BackgroundQuery:=False
.MaintainConnection = False
End With

 

[Tim Williams]

I would guess that method does not support hyperlinks. You would need to
either change your import method, or start storing the hyperlink addresses
in a separate column and use that data to recreate them in the workbook
which does the importing.

Tim

 

[statum]

What method would you suggest? I've never used another method.

 

[Tim Williams]

Why not just open the file and copy the data ?

Tim

 

[statum]

There's a lot going on that I can not put in a post. But I will try to
paraphrase. This macro is importing from several workbooks, not just one. I
only posted it as one b/c what works for one will work for many. It is also
reaching across departmental lines to import data from other excel sources.
It is then combining all data into one database type of workbook used for
charting and statistical analysis as well as historical lookups of customers.
This is where the hyperlinks come in, the manager wants to be able to see the
detailed "call log" data. There are columns in the individual spreadsheets
that allow for hyperlinks to be made. These files, could be word docs or
jpegs, are then easily accessed no matter where they are located on the
network. The program works great except for the hyperlinks do not stick when
the cell data is imported and alpha sorting in autofiltering does not work on
protected sheets. The alpha sort within autofiltering is as big a deal, but I
have posted this as well and have not gotten this resolved either.

 

[Tim Williams]

OK. So why not just open each of the files and copy the data ?
If you want to copy the hyperlinks then you'll have to open the files. I'm
not aware of any other way.
It's really not such a big deal if you turn off screenupdating while you d o
it - won't even be noticeable.

Tim

 

[statum]

Way too many files to open. It would take all day and it should only take
seconds. And then you would have to do it all again next week. That's what a
computer is for. Otherwise, why bother writing macros at all, ever, for
anything.

 

[Tim Williams]

How many files ? This should be doable unless you have thousands.
You don't think I meant open them *manually* do you ?

Tim

 

[statum]

Manually or even atuomatic still takes too long. Each of these workbooks has
its own macro and they are 5MB each. So, it takes a few seconds to actually
open the workbook and run a "health check" on each of the sheets within the
individual workbooks. If that weren't enough each workbook requires a user
input before the macro is completed, so it will just sit and lock down excel
until this is done.
In other words, opening the workbooks is not going to happen.

If this is the only way to copy over hyperlinks, then it sounds like a
design flaw in Excel VB to me. Just as the design flaw of not allowing the
user to adjust the text height and number of lines that appear in a
validation drop down box or allowing alpha sorting without unprotecting cells
within a protected sheet when using autofilter.

 

[Tim Williams]

It's not a design flaw. It just doesn't do what you want it to, the way you
want to do it.
If this is what you need to do, then you have to work around the "design
flaw" and just get it done.
It doesn't need to be fast, it just has to be faster and easier than doing
it manually.

As for the macros which run when you open each of the workbooks, if you're
happy enough pulling the data using the Jet engine, then there's no need to
"health check" each file when you open it: that certainly doesn't happen
with your current method. Just turn off application events and even
calculation and open the files.

Tim

 

[statum]

I have no problems working around "design flaws". I do it every day. However,
I do have a problem with something like this that is obvious. Just like
applying for a patent, if it is an obvious procedure, then it can't be
patented. So, in my opinion, if it is an obvious programming procedure like
auto-copying hyperlinks from one workobook to another, it is a design flaw
and not something a programmer should not have to "work-around". After all
software work-arounds are really nothing more that what has to be done when
the origninal programmer is too lazy to update, change or add to the
instruction set so work-arounds are not necessary. To that end, I cannot
explain all the details of the program procedure to you in a post. That would
require at least a phone call. All you need to know is that I need the
hyperlinks copied with some type of auto-copy procedure, like MS OLEDB JET
4.0 or any other procedure that you may know of, that will do what I need. If
you do not know what that procedure is, or how to do it, then quit spamming
me and trying to re-design our process. All that is doing is confirming my
statment of a design flaw.