0

So, I have an Excel worksheet that contains raw data.

*My goal here is to get data from SQL Server, then copy it to worksheet.

*In order to do that, I have to check if the names from the worksheet exists in database.

Here's my simple Psuedocode:

'Loop thru each row of data in worksheet
   'If Name is found in database,
      'Copy the data from database to Excel worksheet
End Loop

I just started exploring how to connect MS SQL Server with VBA. I've also been researching here about this, but I'm having trouble understanding the threads.

I created a band aid solution for this process. Instead of retrieving the data from database, I created another worksheet as a temporary database. I wanted to do it in SQL Server because each day, the number of data gets bigger.

Would really appreciate if you could help me out on this. Thanks in advance.

bigbryan
  • 411
  • 6
  • 19
  • 36
  • 1
    What is your actual end goal here? Is it to do the comparison in Excel, or the SQL Server? if you've having trouble understand some thins, what is it you don;'t understand? What have you tried so far? Is it working or not as expected (if not, what is it doing instead)? Are you getting an error (if so, what's the error)? This question is rather broad as it stands, you need to try and be more concise. Thanks. – Thom A Apr 06 '18 at 08:22
  • Sorry if my post is not clear enough. My end goal here is to get data from SQL Server, then copy it to worksheet. In order to do that, I have to check if the names from the worksheet exists in database. I created a band aid solution for this process. Instead of retrieving the data from database, I created another worksheet as a temporary database. I wanted to do it in SQL Server because each day, the number of data gets bigger. – bigbryan Apr 06 '18 at 08:32
  • 1
    So, are you wanting to actually do an ETL process from the Spreadsheet to SQL Server and do the comparison in SQL Server? Perhaps it might help it you outline bullet point your goals in your question. The statements *I wanted to do it in SQL Server because each day, the number of data gets bigger. * and *What I wanted to do is, find/retrieve the Names of the raw data from SQL Server.* are a bit contradictory. one implies you want to use Excel to get the data from SQL Server, the other implies the reverse. – Thom A Apr 06 '18 at 08:36
  • Right. Thanks @Larnu. – bigbryan Apr 06 '18 at 08:43

1 Answers1

0

Go through this link to learn more about connection

[https://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba][1]

Copied from link

Sub GetDataFromADO()
'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyRecordset = New ADODB.Recordset
    Dim strSQL As String

'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
    objMyConn.Open

'Set and Excecute SQL Command'
    strSQL = "select * from myTable"

'Open Recordset'
    Set objMyRecordset.ActiveConnection = objMyConn
    objMyRecordset.Open strSQL            

'Copy Data to Excel'
    ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

And then loop through your excel sheet and check wheter data is there in record set or not..

Community
  • 1
  • 1
Aswani Madhavan
  • 816
  • 6
  • 19
  • 1
    strSQL = "select * from myTable where mytable.name = '" & activecell.text & "'" – Harassed Dad Apr 06 '18 at 09:04
  • 1
    I'd suggest against that as well, to be honest. Use Parametrised SQL. Imagine if `activecell.text` had a value of `'; DROP TABLE MyTable;--`. SAy goodbye to `MyTable`! :) – Thom A Apr 06 '18 at 09:32
  • If they can write into a cell they can delete the table by hand anyway - why would they bother with VBA? Parametrised sql is a good idea for this new fangled internet thingy, but it's overkill for a spreadsheet – Harassed Dad Apr 06 '18 at 13:53
  • Awesome. Thanks @AswaniMadhavan. – bigbryan Apr 08 '18 at 15:03