Results 1 to 2 of 2

Update code so user can select any folder and prevent import of data if there is no data

Threaded View

  1. #1
    Registered User
    Join Date
    12-07-2015
    Location
    Greene, NY
    MS-Off Ver
    2010
    Posts
    1

    Update code so user can select any folder and prevent import of data if there is no data

    I have the following code to look in a folder selected by user, and import data from each file stored in that folder. What I'm looking for essentially is updates to my code that will allow user to select any folder regardless if it's the folder the master file is located in. I would also like updates to the code that imports the data so it will not import if a file currently has no data. Any help would be appreciated.
    Sub Select_Folder()
        Dim v_startfolder
        v_startfolder = ThisWorkbook.Path & "\"
        Sheets("Import").Range("D9").Value = f_Pickafolder(ThisWorkbook.Path & "\") & "\"
    End Sub
    Function f_Pickafolder(Optional v_startatthis As Variant) As Variant
        Dim v_obj1 As Object
        Set v_obj1 = CreateObject("Shell.Application"). _
        BrowseForFolder(0, "Select folder to import Excel files", 0, v_startatthis)
        On Error Resume Next
        f_Pickafolder = v_obj1.self.Path
        On Error GoTo 0
        Set v_obj1 = Nothing
        Select Case VBA.Mid(f_Pickafolder, 2, 1)
            Case Is = ":"
            If VBA.Left(f_Pickafolder, 1) = ":" Then GoTo errorsocomehere
                Case Is = "\"
                If Not VBA.Left(f_Pickafolder, 1) = "\" Then GoTo errorsocomehere
                    Case Else
                    GoTo errorsocomehere
                End Select
                Exit Function
    errorsocomehere:
                f_Pickafolder = False
    End Function
    
        
    
    
    
    
    Sub Lets_Prepare_Master()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Dim v_var1 As String, v_var2 As String, v_var3 As Integer
        Dim eb As Workbook
        Dim wb As Workbook
        Dim lrineb As Long
        Dim rineb As Long
        Dim lrinm As Long
        Set wb = ThisWorkbook
        v_var1 = wb.Sheets("Import").Range("D9").Value
        v_var2 = v_var1 & "\*.xls*"
        v_excel = Dir(v_var2)
        Do While v_excel <> ""
            Debug.Print v_excel
            Set eb = Workbooks.Open(wb.Sheets("Import").Range("D9").Value & v_excel)
            lrineb = eb.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
            lrinm = wb.Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row + 1
            eb.Sheets(1).Range("A2:F" & lrineb).Copy _
            Destination:=wb.Sheets("Master").Range("A" & lrinm)
            eb.Close False
            v_excel = Dir()
        Loop
    End Sub
    Last edited by FDibbins; 02-28-2019 at 06:10 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 12-02-2015, 03:32 PM
  2. update code for inserting data in all closed file in folder
    By mokht in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2015, 07:14 PM
  3. VBA code to allow user to select workbook and worksheet for data processing
    By spectrumenginner in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2015, 03:08 PM
  4. Replies: 3
    Last Post: 01-19-2014, 12:27 PM
  5. [SOLVED] How do I update my static line graph to allow a user to select which data to graph
    By capcon in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-31-2011, 10:22 PM
  6. Code to prevent user to Select COLUMNS but allow to select ROWS
    By tengrosita in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2011, 03:46 AM
  7. [SOLVED] Prevent equation from skipping data when referencing import data?
    By lybrt98 in forum Excel General
    Replies: 0
    Last Post: 08-30-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1