VBA or Function - Accessing file with changing name

You can do this with a closed file.  You would have to open it first.

How would I do this with a closed file so that capture the changing file name?

sorry... I had a typo.

meant to say :

You can't do this with a closed file.

Is the limitation that there is a time stamp on the file name or that the file name changes?

the limitation I'm referring to is this:

I would like the update to occur on a closed file as well.

If you have a closed file you cannot read it or write to it.  It can only be read or written to after it has been opened.

Okay that makes sense, so can the source file be opened and closed in the background? Would this require VBA?

Yes this would require VBA.

VBA could look for the most recent excel file in a folder and then open it, and pull data from the opened file, and then close it.

you might not want to use vlookup, because vlookup is a formula that will try to recalculate every time the worksheet recalculates.  If the workbook you are reading from is not open, then the vlookup would fail.

so instead of vlookup, you could have some sort of button to press to refresh the data in your data sheet... or you could execute that code upon opening your data sheet... something like that.

In VBA ... to open a workbook... you could issue the following as an example:
after exection of these 3 lines, the variable wb would reference the wb that you opened.

Dim wb as workbook
Application.Workbooks.Open ("C:\Documents and Settings\Username\Example.xls")
set wb = workbooks.("Example.xls")


Then to close the workbook... you would issue this:

wb.close

Okay, I am thinking that I could copy over the data for the specific brand that I need into a tab in the data file and then do a vlookup off of that data.  The only think I would need to account for is how to copy over the data from the most current source file?

Here is an example

in order for this to run correctly, you need to go into the VBA Editor (Hit ALT-F11) while in your "Data" workbook


Now need to add a reference library so that you have fileSystemObject definitions available to you.  To do this, go to the tools menu, Select "References"  search the list until you find "Microsoft Scripting Runtime" then make sure it has a checkmark in the box next to it, and hit the OK button.

Now on the insert menu,   select a module to insert.

double click "module1" or whatever the name of the module is that you've inserted.

copy and paste in the following code, and modify it for your partilar sitation.

This code will find the newest Excel file in a folder, and copy cells "A1:A2" from that excel file into the Excel file that is currently open, then close the newest workbook.

Option Explicit

Sub test()
    

    Dim fso As FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim newestFile As File
    Dim ws As Worksheet
    
    Set fso = New FileSystemObject
    
    '--------------------------------------------------------------------------------
    'For test purposes, I am using the "My Documents" folder... this needs to change
    ' To use whatever folder you need
    '--------------------------------------------------------------------------------
    
    Set myFolder = fso.GetFolder("C:\users\" & Environ("UserName") & "\Documents")
    
    
    For Each myFile In myFolder.Files
        Select Case UCase(fso.GetExtensionName(myFile.Path))
            Case "XLS", "XLSM", "XLSB", "XLSX":
        
                If newestFile Is Nothing Then
                    Set newestFile = myFile
                ElseIf myFile.DateLastModified > newestFile.DateLastModified Then
                    Set newestFile = myFile
                End If
        End Select
    Next
    
    '--------------------------------------------------------------------------------
    'At this point... "newestFile" is a File object that is the newest Excel File in your folder
    ' The following code will open it, and now you have to copy from whatever range you need data from
    '--------------------------------------------------------------------------------
   
    If Not newestFile Is Nothing Then
        Application.Workbooks.Open newestFile.Path
        Set wb = Application.Workbooks(newestFile.Name)
    'or if you now the name of the sheet it could be something like : Set ws = wb.Sheets("Sheet1")
        Set ws = wb.Sheets(1)
        
        ws.Range("A1:A2").Copy
        ThisWorkbook.Sheets("Sheet1").Range("B5").PasteSpecial xlPasteAll
       wb.close
       MsgBox "Copy Complete"

    End If
    
End Sub

Select allOpen in new window

Getting a compile error: User-defined type not defined on

Dim fso As FileSystemObject

Select allOpen in new window

sounds like you skipped this step :

Now need to add a reference library so that you have fileSystemObject definitions available to you.  To do this, go to the tools menu, Select "References"  search the list until you find "Microsoft Scripting Runtime" then make sure it has a checkmark in the box next to it, and hit the OK button.

If you think you already checked it... just go back through the list of libraries and make sure that "Microsoft Scripting Runtime" is still checked.

Ok have the "Microsoft Scripting Runtime" selected.  Receiving new compile error: variable not defined on

Set wb = Application.Workbooks(newestFile.Name)

Select allOpen in new window

Is there do I need to select something else out of the reference library?

Looks like I missed that somehow.... to fix... add this declaration to the top...

Dim wb As Workbook

no need for any other library.

That worked, I have two more question since I had to alter the way to link to the data, I would like to keep the size of the file small.  How can I copy over only the rows where column A is "Accessories"?

Secondly I get prompted that there is a large amount of information on the Clipboard.  Can the this be by-passed? I don't need to save for later.

Question 1 :

to copy only rows where column A is Accessories...

This sort of depends on where "Accessories" occur.  is it just cell a1 or is it a row label that can occur on every row?

if on every row then loop through each row, check for "Accessories" in column "A" and then copy just that row.

if it is just Cell A1... then you would check Cell A1 for "Accessories before doing the copy.

Let me know if you need help with details of this, but I need more info before I can answer that more specifically.

Question 2 :
you can add this line of code to suppress the alert...

Application.DisplayAlerts = False

The column header is actually Type and "Accessories" is going to be a label that will appear throughout the data only in column A.

If you could help with the loop I would appreciate it.

Thank you so much this will be so helpful will be to adjust so that I can use it in a majority of my reports.