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....

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" wrote in message
...
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....

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 OLEDB:Database P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database 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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....

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" wrote in message
...
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 OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database 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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....

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

"Tim Williams" wrote:

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" wrote in message
...
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 OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database 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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....

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" wrote:

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....

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" wrote in message
...
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" wrote:

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....

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" wrote:

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" wrote in message
...
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" wrote:

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....

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

Tim


On Jul 16, 5:16*am, statum wrote:
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" wrote:
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" wrote in message
...
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" wrote:

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
* * * *, _
* * * *"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
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" _
* * * *, _
* * * *"EDB:Don'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" wrote:

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" wrote in message
...
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....- Hide quoted text -

- Show quoted text -

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" wrote:

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

Tim


On Jul 16, 5:16 am, statum wrote:
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" wrote:
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" wrote in message
...
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" wrote:

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....- Hide quoted text -

- Show quoted text -

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" wrote in message
...
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" wrote:

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

Tim


On Jul 16, 5:16 am, statum wrote:
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" wrote:
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" wrote in message
...
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" wrote:

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet
OLEDB:Database
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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....- Hide quoted text -

- Show quoted text -

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.

"Tim Williams" wrote:

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" wrote in message
...
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" wrote:

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

Tim


On Jul 16, 5:16 am, statum wrote:
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" wrote:
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" wrote in message
...
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" wrote:

Why not just open the file and copy the data ?

Tim

"statum" wrote in message
...
What method would you suggest? I've never used another method.

"Tim Williams" wrote:

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" wrote in message
...
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
OLEDB:Database
P"
_
, _
"assword="""";Jet OLEDB:Engine Type=35;Jet
OLEDB:Database
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" _
, _
"EDB:Don'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" wrote:

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" wrote in message
...
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....- Hide quoted text -

- Show quoted text -