help with vba macro, convert or rewrite to connect to db?

[firstascent]

Hey all,

I currently have an excel spreadsheet that has a macro that displays information from my sql server. Everything works fine with it.

Im trying to embed that into my vb project though. Anyone know what the best route to go would be to do this.

Should I try to convert the vba macro and display the results to a dataset?
Or should I try to recreate the connection through VB to display the results? or is that sort of the same thing? Or should I try something different?

I will post the macro below if that helps give you an idea of what Im doing.

Thanks!

Public Sub GetINFOData()

Dim orderby As String
Dim rstMes As ADODB.Recordset
Dim i As Integer
Application.ScreenUpdating = False


    Server = "ODBC;DSN=TEST"
       
    strSQL = "INFO_Data('" & Format(Sheet1.STime.Value, "dd-mmm-yy HH:MM:SS") & "','" & Format(Sheet1.ETime.Value, "dd-mmm-yy HH:MM:SS") & "')"
        
    Sheets("INFO_Data").Select
    'Sheet5.Activate
    Range("A1:az500").Select
    Selection.Delete
    Cells.Select
    Selection.RowHeight = 16.5
    ActiveSheet.ResetAllPageBreaks
'
    
       With ActiveSheet.QueryTables.Add(Server, _
          Destination:=Range("A4"))
          .Sql = (strSQL)
          .FieldNames = True
          .RefreshStyle = xlInsertDeleteCells
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .RefreshOnFileOpen = False
          .HasAutoFormat = True
          .BackgroundQuery = True
          .TablesOnlyFromHTML = True
          .Refresh BackgroundQuery:=False
          .SavePassword = True
         .SaveData = True
      End With
    
    i = 3
    While Range("A" & i) <> ""
        i = i + 1
    Wend

End Sub

[jmcilhinney]

What you should do is forget the actual code of the macro altogether and just consider the functionality that it provides. You should then write the appropriate code to provide that same functionality in VB.NET. You should go to the Database Development forum and find the FAQ thread at the top, which provides links to various ADO.NET resources.