Excel VBA 2010 - Read a folder and load file name in SQL-Server 2008

Sub ListAllFile()

     

    Dim objFSO As Object

    Dim objFolder As Object

    Dim objFile As Object

    Dim ws As Worksheet

    Dim cnt As Integer

    

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Set ws = Worksheets.Add

    'ActiveSheet.Range("C1").Value

    Set ws = Worksheets("Sheet1")

    cnt = 1

     

     'Get the folder object associated with the directory

    Set objFolder = objFSO.GetFolder("\\abcserver\Fin\Report Output")

    ws.Cells(1, 1).Value = "The files found in This Folder are:- " ' & objFolder.Name & "are:"

    

     'Loop through the Files collection

    For Each objFile In objFolder.Files

        ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name

        'Debug.Print objFile

        rptPathName = objFile.Name

        Call InsertRecord(cnt, rptPathName)

    cnt = cnt + 1

    Next

     

     'Clean up!

    Set objFolder = Nothing

    Set objFile = Nothing

    Set objFSO = Nothing

     

End Sub

Sub InsertRecord(strcnt, strFilePathName)

            Dim adoCN As ADODB.Connection

            Dim sConnString As String

            Dim sSQL As String

            Dim lRow As Long, lCol As Long

            Dim recnt As Integer

            Dim RecRow As Integer

            Dim tbl As Range

            Dim FileName

            Set adoCN = CreateObject("ADODB.Connection")

            sConnString = stADO

            adoCN.Open sConnString

            FileName = strFilePathName

            strFilePathName = ActiveSheet.Range("C1").Value & "\" & strFilePathName

            

                         Dim MyString As String

                                        sSQL = "insert into budget.dbo.tbltempReport  select " & strcnt & ",'" & Replace(strFilePathName, "'", "''") & "'" & ""

                                       Debug.Print sSQL

                          adoCN.Execute sSQL

            adoCN.Close

            Set adoCN = Nothing

End Sub

Table structure

CREATE TABLE [dbo].[tbltempReportOutPD](

[ID] [int] NULL,

[ReportName] [nvarchar](1000) NULL

) ON [PRIMARY]