Hiding URL's in web query?
 

I need to hide the URL of my web query for security reasons. Can anyone help
me figure out how I can do this? I don't want users to be able to see the
path of where the data is stored.

Thanks,

Connie

Hiding URL's in web query?
 

Who are you protecting the data from, your users or the outside world?

Obscuring the data source from the uninitiated, but any amatuer, hobbist or
pro would get that location without ever even touching the Excel file.

To answer your question, I need to know where you are using the URL so that
it may be potentially viewed. Is it in a cell, in a macro, userform, data
definition?

Lets assume that it is linked data and that you have created a data range
that points to the URL. I have a macro that refreshes data from CSB files
then removes the data link reference. Now all you have to do is lock the
VBA Project.

sub refreshdata
dim sName as string
clearsheet "Sheet1"
sName = ImportData("c:\My Data Chart.csv"), "Sheet1",
"Some_Data_Range_Name", Array(2, 2, 2))
Call ActiveWorkbook.Names.Add(Name:="Some_Data_Range_Na me",
RefersToR1C1:=ActiveWorkbook.Names(sName).RefersTo R1C1)
ActiveWorkbook.Names(sName).Delete
end sub

Public Sub ClearSheet(SheetName As String)

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

For i = y.QueryTables.Count To 1 Step -1
y.QueryTables(i).Delete
Next i

For i = y.Names.Count To 1 Step -1
y.Names(i).RefersToRange.Clear
y.Names(i).Delete
Next i

y.UsedRange.Clear

End Sub

Private Function ImportData(DataFilePath As String, SheetName As String,
RangeName As String, TextFileColumnDataTypes As Variant) As string

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

y.Range("A1").Select

Set x = y.QueryTables.Add(Connection:="TEXT;" & DataFilePath,
Destination:=Range("A1"))

With x
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = TextFileColumnDataTypes
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.BackgroundQuery = True
End With

ImportData = y.Name & "!" & x.Name

End Function


"Conniemm" wrote in message
...
I need to hide the URL of my web query for security reasons. Can anyone
help
me figure out how I can do this? I don't want users to be able to see the
path of where the data is stored.

Thanks,

Connie

Hiding URL's in web query?
 

The data is stored on a company intranet. I'm trying to hide the location
from the users so that they do not have the ability to know the location of
where the data that appears on that page as well as others exists.

I currently have a web query that refreshes upon opening the Excel file. It
also has pivot tables that auto-refresh. I want users to have the ability to
see the data but not where the data is coming from.

Make more sense?

Thanks for your quick response.

"AMDRIT" wrote:

Who are you protecting the data from, your users or the outside world?

Obscuring the data source from the uninitiated, but any amatuer, hobbist or
pro would get that location without ever even touching the Excel file.

To answer your question, I need to know where you are using the URL so that
it may be potentially viewed. Is it in a cell, in a macro, userform, data
definition?

Lets assume that it is linked data and that you have created a data range
that points to the URL. I have a macro that refreshes data from CSB files
then removes the data link reference. Now all you have to do is lock the
VBA Project.

sub refreshdata
dim sName as string
clearsheet "Sheet1"
sName = ImportData("c:\My Data Chart.csv"), "Sheet1",
"Some_Data_Range_Name", Array(2, 2, 2))
Call ActiveWorkbook.Names.Add(Name:="Some_Data_Range_Na me",
RefersToR1C1:=ActiveWorkbook.Names(sName).RefersTo R1C1)
ActiveWorkbook.Names(sName).Delete
end sub

Public Sub ClearSheet(SheetName As String)

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

For i = y.QueryTables.Count To 1 Step -1
y.QueryTables(i).Delete
Next i

For i = y.Names.Count To 1 Step -1
y.Names(i).RefersToRange.Clear
y.Names(i).Delete
Next i

y.UsedRange.Clear

End Sub

Private Function ImportData(DataFilePath As String, SheetName As String,
RangeName As String, TextFileColumnDataTypes As Variant) As string

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

y.Range("A1").Select

Set x = y.QueryTables.Add(Connection:="TEXT;" & DataFilePath,
Destination:=Range("A1"))

With x
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = TextFileColumnDataTypes
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.BackgroundQuery = True
End With

ImportData = y.Name & "!" & x.Name

End Function


"Conniemm" wrote in message
...
I need to hide the URL of my web query for security reasons. Can anyone
help
me figure out how I can do this? I don't want users to be able to see the
path of where the data is stored.

Thanks,

Connie

Hiding URL's in web query?
 

Still the solution that I provided earlier fixes that for you.

1. delete existing named ranges
2. delete existing queries
3. import data via your web query macro
4. delete the named range of the query (loses reference to the source, (i.e
WebQuery1))
5. create a named range that is your data (i.e. PivotData)
6. be sure that the VBA project is locked, now the users cannot view the
code.


"Conniemm" wrote in message
...
The data is stored on a company intranet. I'm trying to hide the location
from the users so that they do not have the ability to know the location
of
where the data that appears on that page as well as others exists.

I currently have a web query that refreshes upon opening the Excel file.
It
also has pivot tables that auto-refresh. I want users to have the ability
to
see the data but not where the data is coming from.

Make more sense?

Thanks for your quick response.

"AMDRIT" wrote:

Who are you protecting the data from, your users or the outside world?

Obscuring the data source from the uninitiated, but any amatuer, hobbist
or
pro would get that location without ever even touching the Excel file.

To answer your question, I need to know where you are using the URL so
that
it may be potentially viewed. Is it in a cell, in a macro, userform,
data
definition?

Lets assume that it is linked data and that you have created a data range
that points to the URL. I have a macro that refreshes data from CSB
files
then removes the data link reference. Now all you have to do is lock the
VBA Project.

sub refreshdata
dim sName as string
clearsheet "Sheet1"
sName = ImportData("c:\My Data Chart.csv"), "Sheet1",
"Some_Data_Range_Name", Array(2, 2, 2))
Call ActiveWorkbook.Names.Add(Name:="Some_Data_Range_Na me",
RefersToR1C1:=ActiveWorkbook.Names(sName).RefersTo R1C1)
ActiveWorkbook.Names(sName).Delete
end sub

Public Sub ClearSheet(SheetName As String)

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

For i = y.QueryTables.Count To 1 Step -1
y.QueryTables(i).Delete
Next i

For i = y.Names.Count To 1 Step -1
y.Names(i).RefersToRange.Clear
y.Names(i).Delete
Next i

y.UsedRange.Clear

End Sub

Private Function ImportData(DataFilePath As String, SheetName As String,
RangeName As String, TextFileColumnDataTypes As Variant) As string

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

y.Range("A1").Select

Set x = y.QueryTables.Add(Connection:="TEXT;" & DataFilePath,
Destination:=Range("A1"))

With x
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = TextFileColumnDataTypes
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.BackgroundQuery = True
End With

ImportData = y.Name & "!" & x.Name

End Function


"Conniemm" wrote in message
...
I need to hide the URL of my web query for security reasons. Can anyone
help
me figure out how I can do this? I don't want users to be able to see
the
path of where the data is stored.

Thanks,

Connie

Hiding URL's in web query?
 

Thanks, Amdrit. We think this will work great for us. I appreciate your
replies.

"AMDRIT" wrote:

Still the solution that I provided earlier fixes that for you.

1. delete existing named ranges
2. delete existing queries
3. import data via your web query macro
4. delete the named range of the query (loses reference to the source, (i.e
WebQuery1))
5. create a named range that is your data (i.e. PivotData)
6. be sure that the VBA project is locked, now the users cannot view the
code.


"Conniemm" wrote in message
...
The data is stored on a company intranet. I'm trying to hide the location
from the users so that they do not have the ability to know the location
of
where the data that appears on that page as well as others exists.

I currently have a web query that refreshes upon opening the Excel file.
It
also has pivot tables that auto-refresh. I want users to have the ability
to
see the data but not where the data is coming from.

Make more sense?

Thanks for your quick response.

"AMDRIT" wrote:

Who are you protecting the data from, your users or the outside world?

Obscuring the data source from the uninitiated, but any amatuer, hobbist
or
pro would get that location without ever even touching the Excel file.

To answer your question, I need to know where you are using the URL so
that
it may be potentially viewed. Is it in a cell, in a macro, userform,
data
definition?

Lets assume that it is linked data and that you have created a data range
that points to the URL. I have a macro that refreshes data from CSB
files
then removes the data link reference. Now all you have to do is lock the
VBA Project.

sub refreshdata
dim sName as string
clearsheet "Sheet1"
sName = ImportData("c:\My Data Chart.csv"), "Sheet1",
"Some_Data_Range_Name", Array(2, 2, 2))
Call ActiveWorkbook.Names.Add(Name:="Some_Data_Range_Na me",
RefersToR1C1:=ActiveWorkbook.Names(sName).RefersTo R1C1)
ActiveWorkbook.Names(sName).Delete
end sub

Public Sub ClearSheet(SheetName As String)

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

For i = y.QueryTables.Count To 1 Step -1
y.QueryTables(i).Delete
Next i

For i = y.Names.Count To 1 Step -1
y.Names(i).RefersToRange.Clear
y.Names(i).Delete
Next i

y.UsedRange.Clear

End Sub

Private Function ImportData(DataFilePath As String, SheetName As String,
RangeName As String, TextFileColumnDataTypes As Variant) As string

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

y.Range("A1").Select

Set x = y.QueryTables.Add(Connection:="TEXT;" & DataFilePath,
Destination:=Range("A1"))

With x
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = TextFileColumnDataTypes
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.BackgroundQuery = True
End With

ImportData = y.Name & "!" & x.Name

End Function


"Conniemm" wrote in message
...
I need to hide the URL of my web query for security reasons. Can anyone
help
me figure out how I can do this? I don't want users to be able to see
the
path of where the data is stored.

Thanks,

Connie

Hiding URL's in web query?
 

Connie

I think if you lock the cells and protect the sheet, the users wouldn't be
able to edit the query. Is there some other way they can see the URL?

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

Conniemm wrote:
Thanks, Amdrit. We think this will work great for us. I appreciate
your replies.

"AMDRIT" wrote:

Still the solution that I provided earlier fixes that for you.

1. delete existing named ranges
2. delete existing queries
3. import data via your web query macro
4. delete the named range of the query (loses reference to the
source, (i.e WebQuery1))
5. create a named range that is your data (i.e. PivotData)
6. be sure that the VBA project is locked, now the users cannot
view the code.


"Conniemm" wrote in message
...
The data is stored on a company intranet. I'm trying to hide the
location from the users so that they do not have the ability to
know the location of
where the data that appears on that page as well as others exists.

I currently have a web query that refreshes upon opening the Excel
file. It
also has pivot tables that auto-refresh. I want users to have the
ability to
see the data but not where the data is coming from.

Make more sense?

Thanks for your quick response.

"AMDRIT" wrote:

Who are you protecting the data from, your users or the outside
world?

Obscuring the data source from the uninitiated, but any amatuer,
hobbist or
pro would get that location without ever even touching the Excel
file.

To answer your question, I need to know where you are using the
URL so that
it may be potentially viewed. Is it in a cell, in a macro,
userform, data
definition?

Lets assume that it is linked data and that you have created a
data range that points to the URL. I have a macro that refreshes
data from CSB files
then removes the data link reference. Now all you have to do is
lock the VBA Project.

sub refreshdata
dim sName as string
clearsheet "Sheet1"
sName = ImportData("c:\My Data Chart.csv"), "Sheet1",
"Some_Data_Range_Name", Array(2, 2, 2))
Call ActiveWorkbook.Names.Add(Name:="Some_Data_Range_Na me",
RefersToR1C1:=ActiveWorkbook.Names(sName).RefersTo R1C1)
ActiveWorkbook.Names(sName).Delete
end sub

Public Sub ClearSheet(SheetName As String)

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

For i = y.QueryTables.Count To 1 Step -1
y.QueryTables(i).Delete
Next i

For i = y.Names.Count To 1 Step -1
y.Names(i).RefersToRange.Clear
y.Names(i).Delete
Next i

y.UsedRange.Clear

End Sub

Private Function ImportData(DataFilePath As String, SheetName As
String, RangeName As String, TextFileColumnDataTypes As Variant)
As string

Dim x As QueryTable
Dim y As Worksheet
Dim z As Workbook
Dim i As Integer

Set z = ActiveWorkbook
Set y = z.Sheets(SheetName)

y.Range("A1").Select

Set x = y.QueryTables.Add(Connection:="TEXT;" & DataFilePath,
Destination:=Range("A1"))

With x
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = TextFileColumnDataTypes
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.BackgroundQuery = True
End With

ImportData = y.Name & "!" & x.Name

End Function


"Conniemm" wrote in message
...
I need to hide the URL of my web query for security reasons. Can
anyone help
me figure out how I can do this? I don't want users to be able
to see the
path of where the data is stored.

Thanks,

Connie