Consulting

Results 1 to 4 of 4

Thread: First VBA project summary file please help

  1. #1
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    2
    Location

    Lightbulb First VBA project summary file please help

    Hey Guys,

    This is my first time using VBA so I'd be really grateful for any help/advice. I've done a few online tutorials so have learnt the basics but this is my first project as such.

    I'm looking to create a code which is able to copy data out of one file and place into a summary folder.

    For example, I have files such as 1056, 1057 through to 1186. Within these workbooks, I need to copy data out of a sheet which the name is velocity and the data is in cells C617. It would great to also put the file name above the data. The data needs copied into a new workbook which is called summary.

    Any help to get me up and running with this project would be greatly appreciated,

    Thank you

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Here's some code to get U started. U will need to adjust your file path to suit. The info will come from the Velocity sheet C1:C17 and be placed in the summary workbook sheet1 "A1" (if the code is placed in sheet code and run from the summary workbook.) If you need to run code from a separate wb then that's different. This will get U started. If this works, it's not that hard to put your file names into an array then loop though the array to get the data from all files placed into your summary wb. U will need to specify where all this data goes in the summary wb. HTH. Dave
    Sub test()
    Dim FilDir As Object, fso As Object
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set fso = CreateObject("scripting.filesystemobject")
    '***change File path to your file
    Set FilDir = fso.GetFile(ThisWorkbook.Path & "\yourfoldername\1056.xlsm")
    workbooks.Open filename:=FilDir
    '***change range to suit
    workbooks(FilDir.Name).Sheets("Velocity").Range("C6:C17").Copy _
        Destination:=ThisWorkbook.Sheets("Sheet1").Range("A1")
    Application.CutCopyMode = False
    workbooks(FilDir.Name).Close SaveChanges:=False
    Set FilDir = Nothing
    Set fso = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    ps. Welcome to this forum!

  3. #3
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    2
    Location
    Hi Dave, thanks so much for getting back to me!

    I seem to be having a problem the file path location, the workbook I'm trying to extract data from is a macro-enabled file would this be an issue?

  4. #4
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Put this code in one of your source wbs....
    Private Sub GetWBFilepath()
    MsgBox Application.ActiveWorkbook.FullName
    End Sub
    Replace this part of the posted code with the message box contents as shown below.....
    change this...
    Set FilDir = fso.GetFile(ThisWorkbook.Path & "\yourfoldername\1056.xlsm")
    to this..
    Set FilDir = fso.GetFile(" Place msgbox output here ")
    The problem is usually a file path thing, The macro enable shouldn't matter.
    Dave

Tags for this Thread

Posting Permissions

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