Run .txt file to read as SQL query

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
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?

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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You could use something like this to read the SQL in the file into a variable,
Code:
Dim strSQL As String
Dim rw As String
Dim FF As Integer

    FF = FreeFile

    Open FileName For Input As FF

    Do Until EOF(FF)
        Line Input #FF, rw
        strSQL = strSQL & rw & vbNewLine
    Loop

    Close #FF
and then execute the SQL in the usual way.
 

dboone25

Board Regular
Joined
May 8, 2015
Messages
185
Thanks for the response Norie,

Going to ask a silly question as I get a run-time error '75' ( Path/File access error) would I have to include .GetOpenFilename to select the text file?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
The code I posted would go after the GetOpenfilename section of your code.

In fact the code I posted uses the variable Filename which should include the path and filename for the text file the user selected.
 

dboone25

Board Regular
Joined
May 8, 2015
Messages
185

ADVERTISEMENT

Thanks for the response Norie,

Going to ask a silly question as I get a run-time error '75' ( Path/File access error) would I have to include .GetOpenFilename to select the text file?


Ok still stuck with this one...


First part of the code opens up a dialog box to select the text file...

Code:
myFile = "TXTFiles (*.txt),*.txt"
'Title = "Select .TXT File to Import"
FileName = Application.GetOpenFilename(title:="Select .txt File to Import")


I have added 'FileName For Input As FF' straight after the 'declare variables' as seen..

Code:
'Declare variables'
    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset


Dim strSQL As String
Dim rw As String
Dim FF As Integer

    FF = FreeFile

    Open FileName For Input As FF

    Do Until EOF(FF)
        Line Input #FF, rw
        strSQL = strSQL & rw & vbNewLine
    Loop

    Close #FF


Then the rest of the connection string as...

HTML:
'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


I have changed the section where instead of declaring the SQL text to execute the command using....

HTML:
objMyCmd.strSQL


But still get a run time error '438' Object doesnt support this property or method.

Could appreciate some help with this...little lost..again..sorry.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top