+ Reply to Thread
Results 1 to 3 of 3

vb code to select all files from a folder without manual intervention

  1. #1
    Registered User
    Join Date
    01-02-2009
    Location
    dubai, UAE
    MS-Off Ver
    Excel 2007
    Posts
    1

    vb code to select all files from a folder without manual intervention

    I know nothing in maacro and codes but interested to use someones sweat,

    i got a macro from one of the groups and i use for extracting data from many excel workbooks containing only one worksheet, (inspection request with typical data). here, to extract data required manual intervention to open the folder and selecting files. i need macro to select those files from a given folder without any manual intervention. if someone

    thanks in advance








    Sub Summary_cells_from_Different_Workbooks_2()
    'This example use the function LastRow
    Dim FileNameXls As Variant
    Dim SummWks As Worksheet
    Dim ColNum As Integer
    Dim myCell As Range, Rng As Range, fndFileName As Range
    Dim RwNum As Long, FNum As Long, FinalSlash As Long
    Dim ShName As String, PathStr As String
    Dim SheetCheck As String, JustFileName As String
    Dim JustFolder As String

    ShName = "inspection request" '<---- Change
    Set Rng = Range("B3:B46") '<---- Change

    'Select the files with GetOpenFilename
    FileNameXls = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)



    If IsArray(FileNameXls) = False Then
    'do nothing
    Else
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With

    'Use this sheet for the Summary
    Set SummWks = Sheets("log") '<---- Change

    For FNum = LBound(FileNameXls) To UBound(FileNameXls)
    ColNum = 1
    RwNum = LastRow(SummWks) + 1
    FinalSlash = InStrRev(FileNameXls(FNum), "\")
    JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
    JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)

    'If the workbook name already exist the row color will be Blue
    Set fndFileName = Nothing
    Set fndFileName = SummWks.Cells.Find(JustFileName)
    If Not fndFileName Is Nothing Then
    SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
    .Interior.Color = vbGreen
    Else
    'Do nothing
    End If

    'copy the workbook name in column A
    SummWks.Cells(RwNum, 1).Value = JustFileName

    'build the formula string
    JustFileName = WorksheetFunction.Substitute(JustFileName, "'", "''")
    PathStr = "'" & JustFolder & "\[" & JustFileName & "]" _
    & ShName & "'!"

    On Error Resume Next
    SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1") _
    .Address(, , xlR1C1))
    If Err.Number <> 0 Then
    'If the sheet name not exist the row color will be Yellow.
    SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
    .Interior.Color = vbYellow
    Else
    'Insert the formulas
    For Each myCell In Rng.Cells
    ColNum = ColNum + 1
    SummWks.Cells(RwNum, ColNum).Formula = "=" _
    & PathStr & myCell.Address
    Next myCell
    End If
    On Error GoTo 0
    Next FNum

    ' Use AutoFit to set the column width
    SummWks.UsedRange.Columns.AutoFit

    With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End If
    End Sub


    Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
    After:=sh.Range("A1"), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    On Error GoTo 0
    End Function

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: vb code to select all files from a folder without manual intervention

    Hi ravivaliya,

    the forum software strips out indentation and additional blanks when you just copy and paste code. In order to show it in a nice, readable and copy-ready format, please use code tags when you post code, like this
    [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

    This will make it much easier for people to understand your code and you will get an answer much quicker. You can edit your post to add code tags now.

    cheers
    Last edited by teylyn; 12-08-2009 at 03:09 AM. Reason: typo

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: vb code to select all files from a folder without manual intervention

    ravivaliya,

    please do not use visitor messaging to ask questions. The links at the top of this window have a How To ... menu. In there you'll find

    How to ... Use code tags

    http://www.excelforum.com/misc.php?do=bbcode#code

    regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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