Good mornig all.
Trying to figure out a way to read an SQL script that is saved in a .txt format and then opened through VBA if that is even possible.
Had a search on this fantastic thread but unfortunately could not find a solution.
I have tried opening the text file with myconnection string but this comes up with an error ' object does not support this method'.
Has anyone had any luck with something similar?
Trying to figure out a way to read an SQL script that is saved in a .txt format and then opened through VBA if that is even possible.
Had a search on this fantastic thread but unfortunately could not find a solution.
I have tried opening the text file with myconnection string but this comes up with an error ' object does not support this method'.
Has anyone had any luck with something similar?
HTML:
Private Sub CommandButton4_Click()
' Declare variables.
Dim myFile As String
Dim FileName As Variant
Dim r As Range
Dim i As Long
Dim rc As Range
Dim rr As Long
' Select .txt file.
' Open dialog box title.
' Add title to open file dialogue box " Select .csv file to import
myFile = "CSV Files (*.txt),*.txt"
'Title = "Select .TXT File to Import"
FileName = Application.GetOpenFilename(title:="Select .txt File to Import")
' Select and open the dialog box.
' If no file selected open dialog box.
If FileName = False Then
MsgBox "No File Selected"
Exit Sub
End If
'Declare variables'
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
'Open Connection'
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=np-2;Initial Catalog=TESTDB;Integrated Security=SSPI;"
objMyConn.Open
'Set and Excecute SQL Command'
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.myFile
'objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Open Recordset'
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
'Loop Names'
' Loop to include SQL headers
Dim title As Integer
'Copy Data to Excel'
Workbooks.Add.Worksheets("Sheet1").Range("A2").CopyFromRecordset (objMyRecordset)
For title = 0 To objMyRecordset.fields.count - 1
ActiveWorkbook.Sheets("Sheet1").Cells(1, title + 1) = objMyRecordset.fields(title).Name
Next title
'Close connection and open messagebox'
objMyRecordset.Close
objMyConn.Close
Set objMyRecordset = Nothing
MsgBox "Complete" ', vbOKOnly, "Finished"
End Sub