Monday, July 22, 2013

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]

No comments: