• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Shows how to create a .csv file and store all data from a SQL Server Database table.

Shows how to create a .csv file and store all data from a SQL Server Database table.

Shows how to create a .csv file and store all data from a SQL Server Database table.

API Declarations


'Microsoft ActiveX Data Objects 2.1 Object Library &
'Microsoft Excel 8.0

Dim myWo As New Excel.Application
Dim myData As New ADODB.Connection
Dim myRecset As New ADODB.Recordset




Rate Shows how to create a .csv file and store all data from a SQL Server Database table.

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Public Function OpenDB(DBPath As String, RecPath As String, CSVFileName As String)
On Error Resume Next
myData.ConnectionString = "Provider=SQLOLEDB;Data source=myservername;UID=SA;pwd=;DataBase=" _
& DBPath

myData.Open

myRecset.CursorLocation = adUseClient
myRecset.LockType = adLockOptimistic
myRecset.CursorType = adOpenDynamic

myRecset.Open RecPath, myData

myWo.Workbooks.Add
myWo.Worksheets.Add
myWo.DisplayAlerts = False
myWo.Visible = False


For fname = 0 To myRecset.Fields.Count - 1
    
    myWo.ActiveCell(1, fname + 1) = myRecset.Fields(fname).Name
    myWo.ActiveCell(1, fname + 1).Font.Bold = True
Next


For i = 1 To myRecset.RecordCount
        For j = 1 To myRecset.Fields.Count
            If IsNull(myRecset.Fields(j)) Then
                x = y
            Else
        myWo.ActiveCell(i + 1, j) = myRecset.Fields(j - 1)
End If

Next j
    myRecset.MoveNext
Next i

myWo.ActiveWorkbook.SaveAs (CSVFileName)
myWo.ActiveWorkbook.Close
End Function

Private Sub Form_Load()
Call Opendb("pubs", "titles", "D:\karada.csv")
End Sub