vlookup to a table on a website

[Roger on Excel]

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

 

[RB Smissaert]

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

 

[RB Smissaert]

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

 

[Roger on Excel]

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]

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