Using SQL Select Statement in VBA

Question

I'm very new to VBA and wondering if I can use it to speed up a project. Here is the scenario: I have a very old system in which I need to enter 1000s of entrees from a spreadsheet. The system asks for one piece of information that I need to manually enter from a spreadsheet. I'm wondering if I can use VBA to search the spreadsheet and pull the data from column Y where column X = "1". The system does have a macro function in which I can modify the VBA code.

Basically, what I want to happen is to run a macro that triggers a SQL select statement which states SELECT Y WHERE X EQUALS (Enter Data Number).

Below is the best I can do by way of laying out the existing VBA code. My apologies for my ignorance.

Sub Test()
    With Session
        .Transmit "Select Column Y from Example Spreadsheet where Column X Equals (Enter Data Number)"
        ' Press HpEnter (Simulate pressing the Enter key).
        .TransmitTerminalKey rcHpEnterKey
Exit Sub

I'm not sure I understand your architecture. Are you saying that you have items sitting in a spreadsheet that you want to search for in SQL Server? — Pat Jones
– Pat Jones, Commented Nov 28, 2017 at 19:50
I have an old server that I would like to have reference an Excel file. — Colin
– Colin, Commented Nov 28, 2017 at 21:50

S Meaden · Accepted Answer · 2017-11-28 20:36:24Z

1

This is possible using a special OLE DB Provider that allows running SQL queries over spreadsheet tables. I have a blog post about it here

http://exceldevelopmentplatform.blogspot.co.uk/2017/02/using-2007-office-system-driver-to.html

answered Nov 28, 2017 at 20:36

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SQL Select Statement in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related