Attribute VB_Name = "Drop_Make_AllZillowTables"
Private FileRows As Long
Sub ATTEMPT()
Sheets("Sheet1").Activate
Dim cell As Range
Dim MyRNG As Range: Set MyRNG = Range("B3:B94")
Dim MyFile_Name As String
Dim SQLCommand As String
Dim SQLCommand2 As String
For Each cell In MyRNG
    If Left(cell.Value, 10) = "Zip_Median" Then
        Drop_Make_AllZillowTables.ADOReadCSVFile (cell.Value)
        MyFile_Name = cell.Value
        MyFile_Name = Left(MyFile_Name, Len(MyFile_Name) - 4)
        Debug.Print "--" & MyFile_Name & " Table is dropped and created here"
    
        SQLCommand = vbNewLine & " DROP TABLE IF EXISTS GeoCityDB.dbo." & MyFile_Name & vbNewLine
        SQLCommand = SQLCommand & " CREATE TABLE GeoCityDB.dbo." & MyFile_Name
        SQLCommand = SQLCommand & " (MonthDate VARCHAR(100) NULL,ZipCode VARCHAR(25) NULL," & MyFile_Name
        SQLCommand = SQLCommand & " VARCHAR(50) NULL)" & vbNewLine & " ON [PRIMARY]"
        SQLCommand = SQLCommand & " BULK INSERT GeoCityDB.dbo." & MyFile_Name & vbNewLine
        SQLCommand = SQLCommand & " FROM 'C:\Users\Allen\Documents\Visual Studio 2015\Projects\GeoDBFile\data\" & MyFile_Name & ".csv'"
        SQLCommand = SQLCommand & " WITH(FIRSTROW=1,FIELDTERMINATOR = ',',LASTROW=" & FileRows & " ,ROWTERMINATOR = '0x0a');"
        Debug.Print SQLCommand
        Debug.Print
        SQLCommand2 = SQLCommand2 & vbNewLine & SQLCommand
    End If
Next cell
Sheets.Add: Range("A1:G3").Cells.Merge: Range("A1").Value = SQLCommand2
End Sub
Sub ADOReadCSVFile(CSV_FILE As String)
'Const CSV_FILE As String = "TESTFILE.txt"
Dim strPathtoTextFile As String
'set reference to Microsoft ActiveX Data Objects Library (Tools>References...)
Dim objConnection As ADODB.Connection
Dim objRecordset As ADODB.Recordset

Set objConnection = New ADODB.Connection
Set objRecordset = New ADODB.Recordset
'strPathtoTextFile = ThisWorkbook.Path & "\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Users\Allen\Documents\Visual Studio 2015\Projects\GeoDBFile\data\;" & _
"Extended Properties=""text;HDR=No;FMT=CSVDelimited"""

objRecordset.Open "SELECT * FROM " & CSV_FILE, objConnection, adOpenStatic, adLockOptimistic, adCmdText

If Not objRecordset.EOF Then FileRows = objRecordset.RecordCount 'Debug.Print CSV_FILE & ": " & objRecordset.RecordCount

objRecordset.Close 'Close ADO objects
objConnection.Close

Set objRecordset = Nothing
Set objConnection = Nothing
End Sub