Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers

The simpliest thig to do is to record a macro when performing the opration
manually.

1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.


You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.



"scubadiver" wrote:

Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers

It is a bit more complicated than that and I am staying with the easiest
option.

cheers

"Joel" wrote:

The simpliest thig to do is to record a macro when performing the opration
manually.

1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.


You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.



"scubadiver" wrote:

Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers

There seems to be an error in you r lastt posting. Can't read the message.

"Joel" wrote:

The simpliest thig to do is to record a macro when performing the opration
manually.

1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.


You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.



"scubadiver" wrote:

Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers

I have a list of postcodes in Excel and I want to use VBA to get information
from a table in SQL server which has almost 2million rows.

I would really like to know what code I need to use to get the required
records to match the postcodes.

Does that make sense?

"Joel" wrote:

There seems to be an error in you r lastt posting. Can't read the message.

"Joel" wrote:

The simpliest thig to do is to record a macro when performing the opration
manually.

1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.


You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.



"scubadiver" wrote:

Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers

There are a number of ways of getting data from Access. It depends if it is
ADO vs DBO and if you want to perform a query, get a table, or use SQL
language. I'm not sure how familar you are with SQL but here is a simple
method.


The SQL statement needs to be modified "Select * from [Case]"

The SQL statement so this statment is a sting and can be modified to include
where

"Select * from [postcodes] where ([postodes] like 10001"

or

for RowCount = 1 to 100
Zipcode = Range("A" & Rowcount)
"Select * from [postcodes] where ([postodes] like " & Zipcode
next RowCount

for code below to run you need to add reference libraries to you VBA window
menu

Tools - References

check the following items

Microsoft Access 11.0 Ojbect Library
Microsoft ActiveX Data Objects 2.8 library

Press OK.
Use the latest version of the library on your system.




--------------------------------------------------------------------------------------------

Option Explicit

Sub TestOne()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long
Dim myrow As String

ThisWorkbook.Sheets(3).Cells.ClearContents


cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn



Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If

myrow = rs.GetString(StringFormat:=adClipString, _
numrows:=1, ColumnDelimeter:=",")
ThisWorkbook.Sheets(3).Cells(i, 1) = myrow
rs.MoveNext
Next i
Loop

rs.Close
cn.Close

End Sub

"scubadiver" wrote:


I have a list of postcodes in Excel and I want to use VBA to get information
from a table in SQL server which has almost 2million rows.

I would really like to know what code I need to use to get the required
records to match the postcodes.

Does that make sense?

"Joel" wrote:

There seems to be an error in you r lastt posting. Can't read the message.

"Joel" wrote:

The simpliest thig to do is to record a macro when performing the opration
manually.

1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.


You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.



"scubadiver" wrote:

Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers

it isn't access, it is SQL server not that probably makes much difference.

"Joel" wrote:

There are a number of ways of getting data from Access. It depends if it is
ADO vs DBO and if you want to perform a query, get a table, or use SQL
language. I'm not sure how familar you are with SQL but here is a simple
method.


The SQL statement needs to be modified "Select * from [Case]"

The SQL statement so this statment is a sting and can be modified to include
where

"Select * from [postcodes] where ([postodes] like 10001"

or

for RowCount = 1 to 100
Zipcode = Range("A" & Rowcount)
"Select * from [postcodes] where ([postodes] like " & Zipcode
next RowCount

for code below to run you need to add reference libraries to you VBA window
menu

Tools - References

check the following items

Microsoft Access 11.0 Ojbect Library
Microsoft ActiveX Data Objects 2.8 library

Press OK.
Use the latest version of the library on your system.




--------------------------------------------------------------------------------------------

Option Explicit

Sub TestOne()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long
Dim myrow As String

ThisWorkbook.Sheets(3).Cells.ClearContents


cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn



Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If

myrow = rs.GetString(StringFormat:=adClipString, _
numrows:=1, ColumnDelimeter:=",")
ThisWorkbook.Sheets(3).Cells(i, 1) = myrow
rs.MoveNext
Next i
Loop

rs.Close
cn.Close

End Sub

"scubadiver" wrote:


I have a list of postcodes in Excel and I want to use VBA to get information
from a table in SQL server which has almost 2million rows.

I would really like to know what code I need to use to get the required
records to match the postcodes.

Does that make sense?

"Joel" wrote:

There seems to be an error in you r lastt posting. Can't read the message.

"Joel" wrote:

The simpliest thig to do is to record a macro when performing the opration
manually.

1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.


You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.



"scubadiver" wrote:

Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers

Lets go back to what I said previously with a little modificationn. I think
it is best that you get it working manually while recording a macro and then
make refinements. Get all the data then limit it to the codes you want.

Try going to Data - Import External Data - Import Data. Then select New SQL
server. See if you can get any data from the server using this method. Then
post the record macro. The SQL statement probably will need a little
modification.

"scubadiver" wrote:


it isn't access, it is SQL server not that probably makes much difference.

"Joel" wrote:

There are a number of ways of getting data from Access. It depends if it is
ADO vs DBO and if you want to perform a query, get a table, or use SQL
language. I'm not sure how familar you are with SQL but here is a simple
method.


The SQL statement needs to be modified "Select * from [Case]"

The SQL statement so this statment is a sting and can be modified to include
where

"Select * from [postcodes] where ([postodes] like 10001"

or

for RowCount = 1 to 100
Zipcode = Range("A" & Rowcount)
"Select * from [postcodes] where ([postodes] like " & Zipcode
next RowCount

for code below to run you need to add reference libraries to you VBA window
menu

Tools - References

check the following items

Microsoft Access 11.0 Ojbect Library
Microsoft ActiveX Data Objects 2.8 library

Press OK.
Use the latest version of the library on your system.




--------------------------------------------------------------------------------------------

Option Explicit

Sub TestOne()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long
Dim myrow As String

ThisWorkbook.Sheets(3).Cells.ClearContents


cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn



Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If

myrow = rs.GetString(StringFormat:=adClipString, _
numrows:=1, ColumnDelimeter:=",")
ThisWorkbook.Sheets(3).Cells(i, 1) = myrow
rs.MoveNext
Next i
Loop

rs.Close
cn.Close

End Sub

"scubadiver" wrote:


I have a list of postcodes in Excel and I want to use VBA to get information
from a table in SQL server which has almost 2million rows.

I would really like to know what code I need to use to get the required
records to match the postcodes.

Does that make sense?

"Joel" wrote:

There seems to be an error in you r lastt posting. Can't read the message.

"Joel" wrote:

The simpliest thig to do is to record a macro when performing the opration
manually.

1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.


You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.



"scubadiver" wrote:

Hello,

I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server

I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?

cheers