I have a detailed table used for vlookup in my spreadsheet.

Trouble is, the table is too big and makes the spreadsheet very large.

Is there anyway to use vlookup to access the same data on a webpage i create
to store the large table? It would reduce the size of my spreadsheet by 50%

Thanks,

Roger

Unless you need web access for other reasons that seems an awkward solution
to me.
If the workbook size really is a problem then I would put the data in a text
file or maybe a database like SQLite.

RBS

"Roger on Excel" wrote in message
...
I have a detailed table used for vlookup in my spreadsheet.

Trouble is, the table is too big and makes the spreadsheet very large.

Is there anyway to use vlookup to access the same data on a webpage i
create
to store the large table? It would reduce the size of my spreadsheet by
50%

Thanks,

Roger

Here a most basic example to show how easy it is to do this with SQL on text
files.
The text file in this example is like this:

"Part_Number", "Part_Description"
1,"wheelnut"
2,"doormat"
3,"seatnut"
4,"lightnut"

So, this is the plain text file: C:\TextTables\Parts.txt

Sub TestLateBinding()

Dim rs As Object
Dim TextConn As String
Dim strSQL As String

TextConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"C:\TextTables\;" & _
"Extended Properties=Text;"

strSQL = "SELECT PART_NUMBER FROM PARTS.TXT WHERE " & _
"PART_DESCRIPTION LIKE '%nut'"

Set rs = CreateObject("ADODB.Recordset")

rs.Open Source:=strSQL, _
ActiveConnection:=TextConn, _
CursorType:=0, _
LockType:=1, _
Options:=1

Cells(1).CopyFromRecordset rs

rs.Close
Set rs = Nothing

End Sub


If you use early binding so with a reference to ADO 2.x then the could can
be like this:

Sub TestEarlyBinding()

Dim rs As ADODB.Recordset
Dim TextConn As String
Dim strSQL As String

TextConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"C:\TextTables\;" & _
"Extended Properties=Text;"

strSQL = "SELECT PART_NUMBER FROM PARTS.TXT WHERE " & _
"PART_DESCRIPTION LIKE '%nut'"

Set rs = New ADODB.Recordset

rs.Open Source:=strSQL, _
ActiveConnection:=TextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Cells(1).CopyFromRecordset rs

rs.Close
Set rs = Nothing

End Sub


It has no error handling and you may need to add some more code, but this
will give you the idea.


RBS



"RB Smissaert" wrote in message
...
Unless you need web access for other reasons that seems an awkward
solution to me.
If the workbook size really is a problem then I would put the data in a
text file or maybe a database like SQLite.

RBS

"Roger on Excel" wrote in message
...
I have a detailed table used for vlookup in my spreadsheet.

Trouble is, the table is too big and makes the spreadsheet very large.

Is there anyway to use vlookup to access the same data on a webpage i
create
to store the large table? It would reduce the size of my spreadsheet by
50%

Thanks,

Roger

Thanks for the advice.

This is a little advanced for me - i was hoping to be able to upload my
table to the web and use a web query option to access the data.

I guess its a little more complicated.

I really dont want to have a subsidiary file that needs to be locally
present - ive had enough problems with linked files to be put off by this.

Thanks for your advice

Best regards,

Roger

"RB Smissaert" wrote:

Here a most basic example to show how easy it is to do this with SQL on text
files.
The text file in this example is like this:

"Part_Number", "Part_Description"
1,"wheelnut"
2,"doormat"
3,"seatnut"
4,"lightnut"

So, this is the plain text file: C:\TextTables\Parts.txt

Sub TestLateBinding()

Dim rs As Object
Dim TextConn As String
Dim strSQL As String

TextConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"C:\TextTables\;" & _
"Extended Properties=Text;"

strSQL = "SELECT PART_NUMBER FROM PARTS.TXT WHERE " & _
"PART_DESCRIPTION LIKE '%nut'"

Set rs = CreateObject("ADODB.Recordset")

rs.Open Source:=strSQL, _
ActiveConnection:=TextConn, _
CursorType:=0, _
LockType:=1, _
Options:=1

Cells(1).CopyFromRecordset rs

rs.Close
Set rs = Nothing

End Sub


If you use early binding so with a reference to ADO 2.x then the could can
be like this:

Sub TestEarlyBinding()

Dim rs As ADODB.Recordset
Dim TextConn As String
Dim strSQL As String

TextConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"C:\TextTables\;" & _
"Extended Properties=Text;"

strSQL = "SELECT PART_NUMBER FROM PARTS.TXT WHERE " & _
"PART_DESCRIPTION LIKE '%nut'"

Set rs = New ADODB.Recordset

rs.Open Source:=strSQL, _
ActiveConnection:=TextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Cells(1).CopyFromRecordset rs

rs.Close
Set rs = Nothing

End Sub


It has no error handling and you may need to add some more code, but this
will give you the idea.


RBS



"RB Smissaert" wrote in message
...
Unless you need web access for other reasons that seems an awkward
solution to me.
If the workbook size really is a problem then I would put the data in a
text file or maybe a database like SQLite.

RBS

"Roger on Excel" wrote in message
...
I have a detailed table used for vlookup in my spreadsheet.

Trouble is, the table is too big and makes the spreadsheet very large.

Is there anyway to use vlookup to access the same data on a webpage i
create
to store the large table? It would reduce the size of my spreadsheet by
50%

Thanks,

Roger

Unless the data needs to be updated often (by yourself?) I would think a
local text file is a lot simpler.
Maybe you have to explain what exactly you are dealing with.

RBS


"Roger on Excel" wrote in message
...
Thanks for the advice.

This is a little advanced for me - i was hoping to be able to upload my
table to the web and use a web query option to access the data.

I guess its a little more complicated.

I really dont want to have a subsidiary file that needs to be locally
present - ive had enough problems with linked files to be put off by this.

Thanks for your advice

Best regards,

Roger

"RB Smissaert" wrote:

Here a most basic example to show how easy it is to do this with SQL on
text
files.
The text file in this example is like this:

"Part_Number", "Part_Description"
1,"wheelnut"
2,"doormat"
3,"seatnut"
4,"lightnut"

So, this is the plain text file: C:\TextTables\Parts.txt

Sub TestLateBinding()

Dim rs As Object
Dim TextConn As String
Dim strSQL As String

TextConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"C:\TextTables\;" & _
"Extended Properties=Text;"

strSQL = "SELECT PART_NUMBER FROM PARTS.TXT WHERE " & _
"PART_DESCRIPTION LIKE '%nut'"

Set rs = CreateObject("ADODB.Recordset")

rs.Open Source:=strSQL, _
ActiveConnection:=TextConn, _
CursorType:=0, _
LockType:=1, _
Options:=1

Cells(1).CopyFromRecordset rs

rs.Close
Set rs = Nothing

End Sub


If you use early binding so with a reference to ADO 2.x then the could
can
be like this:

Sub TestEarlyBinding()

Dim rs As ADODB.Recordset
Dim TextConn As String
Dim strSQL As String

TextConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"C:\TextTables\;" & _
"Extended Properties=Text;"

strSQL = "SELECT PART_NUMBER FROM PARTS.TXT WHERE " & _
"PART_DESCRIPTION LIKE '%nut'"

Set rs = New ADODB.Recordset

rs.Open Source:=strSQL, _
ActiveConnection:=TextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Cells(1).CopyFromRecordset rs

rs.Close
Set rs = Nothing

End Sub


It has no error handling and you may need to add some more code, but this
will give you the idea.


RBS



"RB Smissaert" wrote in message
...
Unless you need web access for other reasons that seems an awkward
solution to me.
If the workbook size really is a problem then I would put the data in a
text file or maybe a database like SQLite.

RBS

"Roger on Excel" wrote in
message
...
I have a detailed table used for vlookup in my spreadsheet.

Trouble is, the table is too big and makes the spreadsheet very large.

Is there anyway to use vlookup to access the same data on a webpage i
create
to store the large table? It would reduce the size of my spreadsheet
by
50%

Thanks,

Roger