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