1

I have written a code that searches for and then copies cells from .xls files (Proposals). My problem is that I have E:\Bids(Year)(Project). I recently added code to prompt the user (me) to select the subfolder.
I read vba-macro-that-search-for-file-in-multiple-subfolders but I am new to this. I guess I don't understand how the functions are implemented into the VBA code. Is there a site that I can go to to learn about this? I am more asking for lessons on fishing rather than asking you to provide a fish. Please let me know if I should have posted this as a comment on the above question rather than making a new post.

Sub BidDB()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim sourceRange As Range
Dim DestRange As Range
Dim FileToOpen As String

'Application.ScreenUpdating = False


' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = ActiveWorkbook.ActiveSheet

' Folder than contains the files
'FolderPath = "Z:\Bids\"


' Where to insert new rows in the destination workbook.
NRow = 3

' Dir to find all Excel files in the folder path.
'FileName = Dir(FolderPath & "*.xls")
 FileName = Application.GetOpenFilename("Excel workbooks (*.xls*), *.xls*")


' Loop until Dir returns an empty string.
Do While FileName <> "False"

    'Open a workbook in the folder
    'Set WorkBk = Workbooks.Open(FolderPath & FileName)
     Set WorkBk = Workbooks.Open(FileName)

    ' Set the cell in column A to be the file name.
    SummarySheet.Range("A" & NRow).Value = FileName

    ' Date
    Set sourceRange = WorkBk.Worksheets(1).Range("H4:I4")
    Set DestRange = SummarySheet.Range("B" & NRow)
    Set DestRange = DestRange.Resize(sourceRange.Rows.Count, _
       sourceRange.Columns.Count)
    DestRange.Value = sourceRange.Value

    ' Increase NRow so that we know where to copy data next.
    NRow = NRow + DestRange.Rows.Count

    ' Close the source workbook without saving changes.
    WorkBk.Close savechanges:=False

    ' Use Dir to get the next file name.
    'FileName = Dir()
    FileName = Application.GetOpenFilename("Excel workbooks (*.xls*), *.xls*")

Loop

' Call AutoFit on the destination sheet
SummarySheet.Columns.AutoFit
'Application.ScreenUpdating = True

End Sub

Community
  • 1
  • 1
Beau G. Crandall
  • 11
  • 4
  • Welcome to Stack Overflow. Hopefully you will find answers to your questions. Please don't include unnecessary information in your questions, though, like "thank yous" etc. Stack Overflow questions belong not to the person asking the question but to the community as a whole, and superfluous content is distracting and detracts from the questions. – rory.ap Feb 09 '16 at 15:08
  • @ roryap - Understood – Beau G. Crandall Feb 09 '16 at 15:14
  • @roryap or anyone else that reads this. How do I delete a question? I am so happy that nobody answered my question because it caused me to figure it out through trial and error. – Beau G. Crandall Feb 10 '16 at 05:36

0 Answers0