none
How to Loop the Import of Excel Worksheets RRS feed

  • Question

  • The following code goes to SharePoint, downloads a worksheet from the SharePoint, copies the worksheet to an existing workbook, then renames the downloaded worksheet.

    -----------------------------------------------------------

    Public Sub ImportFileName()

       ' Define Workbook and Worksheet Variables
       Dim wkbMyWorkbook As Workbook
       Dim wkbWebWorkbook As Workbook
       Dim wksWebWorkSheet As Worksheet

       Set wkbMyWorkbook = ActiveWorkbook

       ' Open The Web Workbook
       Workbooks.Open (webaddress_filename_ext)

       ' Set the Web Workbook and Worksheet Variables
       Set wkbWebWorkbook = ActiveWorkbook
       Set wksWebWorkSheet = ActiveSheet

       ' Copy The Web Worksheet, delete if already exists in this Workbook the Insert and ename
       wksWebWorkSheet.Copy after:=wkbMyWorkbook.Sheets(Sheets.Count)
       wkbMyWorkbook.Activate
       Call DeleteFileName
       wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "NewWorksheetsName"

       ' Close the Web Workbook
       wkbMyWorkbook.Activate
       wkbWebWorkbook.Close False

    End Sub
    -----------------------------------------------------------

    I use "Call ImportFileName" to run the code.  Because I have several SharePoints to import the data from, I many repeats of the above code (I just change the FileName and webaddress).

    I would like to revise the above code to loop, so that I don't have to have so many "Public Sub ImportFileName()" and "Call ImportFileName" scripts.

    Thank you for any assistance you are able to provide.

    Rich
    Saturday, April 25, 2015 1:31 PM

Answers

  • Hi Rich,

    I assume that you know all of the web addresses and file names so create a list of the web addresses and files in a worksheet in the same workbook that contains the code. The example code below assumes column A of Sheet3. (The list starts from row 2 because assumes a column header above the list.)

    Then in the sub that calls the code to open and copy loops through the list and calls the sub to process. It passes the filename to the called sub as a parameter.

    Your code calls a delete sub that you did not include but as per the comments in the example you can pass the filename to the Delete code also. Note my comments on whether this is the correct code to delete a file on the web.

    Sub CommenceImport()
        Dim rngFiles As Range
        Dim rngCel As Range
       
        With Worksheets("Sheet3")     'Edit "Sheet3" to the sheet name that you use for the list
            Set rngFiles = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))    'Assumes list in in column A
        End With
       
        For Each rngCel In rngFiles
            Call ImportFileName(rngCel.Value)   'Call the sub pass the FileName as a parameter
        Next

    End Sub

    Public Sub ImportFileName(strFile As String)    'Sub name contains a parameter

       ' Define Workbook and Worksheet Variables
       Dim wkbMyWorkbook As Workbook
       Dim wkbWebWorkbook As Workbook
       Dim wksWebWorkSheet As Worksheet

       Set wkbMyWorkbook = ActiveWorkbook

       ' Open The Web Workbook
       'Workbooks.Open (webaddress_filename_ext)    'Replace this line with following line
       Workbooks.Open (strFile)

       ' Set the Web Workbook and Worksheet Variables
       Set wkbWebWorkbook = ActiveWorkbook
       Set wksWebWorkSheet = ActiveSheet

       ' Copy The Web Worksheet, delete if already exists in this Workbook the Insert and ename
       wksWebWorkSheet.Copy after:=wkbMyWorkbook.Sheets(Sheets.Count)
       wkbMyWorkbook.Activate
      
       'I assume it is the file on web to be deleted
       'if so then you can pass the file name to Sub DeleteFileName
       Call DeleteFileName(strFile)      'Pass the FileName to the Delete sub
       'Call DeleteFileName
      
       wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "NewWorksheetsName"

       ' Close the Web Workbook
       wkbMyWorkbook.Activate
       wkbWebWorkbook.Close False

    End Sub

    Sub DeleteFileName(strFileToDelete As String)     'Sub includes a parameter to accept the file name
        'Example only. May not be correct to delete a file on the web.
        Kill strFileToDelete
    End Sub


    Regards, OssieMac

    • Marked as answer by L.HlModerator Wednesday, May 6, 2015 9:03 AM
    Monday, April 27, 2015 4:59 AM