VBA or Function - Accessing file with changing name

I have a report that is created daily at 5 am,  The source file naming convention is  filename date (YYYY-MM-DD) Timestamp (hh-mm-ss) - ISS 2013-10-14-05-05-41.xls.  Outside of the date change the other variables on the file name are going to be the minutes and seconds.  

My goal is have update a data file that is linked to the source file by vlookup to update based on the most current source file in the folder.  I would like the update to occur on a closed file as well. Is this possible? Could it be done with a function or would it need to be VBA?
jmac001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
You can do this with a closed file.  You would have to open it first.
0
jmac001Author Commented:
How would I do this with a closed file so that capture the changing file name?
0
Ken ButtersCommented:
sorry... I had a typo.

meant to say :

You can't do this with a closed file.
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

jmac001Author Commented:
Is the limitation that there is a time stamp on the file name or that the file name changes?
0
Ken ButtersCommented:
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.
0
jmac001Author Commented:
Okay that makes sense, so can the source file be opened and closed in the background? Would this require VBA?
0
Ken ButtersCommented:
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
0
jmac001Author Commented:
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?
0
Ken ButtersCommented:
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

Open in new window

0
jmac001Author Commented:
Getting a compile error: User-defined type not defined on

Dim fso As FileSystemObject

Open in new window

0
Ken ButtersCommented:
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.
0
jmac001Author Commented:
Ok have the "Microsoft Scripting Runtime" selected.  Receiving new compile error: variable not defined on


Set wb = Application.Workbooks(newestFile.Name)

Open in new window


Is there do I need to select something else out of the reference library?
0
Ken ButtersCommented:
Looks like I missed that somehow.... to fix... add this declaration to the top...

Dim wb As Workbook

no need for any other library.
0
jmac001Author Commented:
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.
0
Ken ButtersCommented:
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
0
jmac001Author Commented:
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.
0
Ken ButtersCommented:
This is an example of a loop...

it will examine the first cell in each row and see if it equals "Accessories".  if it does it will copy the row.



Option Explicit

Sub test()
    

    Dim fso As FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim newestFile As File
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lastDestCell As Range
    Dim lastSourceCell As Range
    Dim destinationRow As Long
    Dim i As Long
    
    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)
        
        Set lastSourceCell = LastCell(ws)
        If lastSourceCell Is Nothing Then
            MsgBox "Nothing to copy - stopping"
            wb.Close
            Exit Sub
        End If
        
        Set lastDestCell = LastCell(ThisWorkbook.Sheets("Sheet1"))
        If lastDestCell Is Nothing Then
            destinationRow = 1
        Else
            destinationRow = lastDestCell.Row + 1
        End If
        
        For i = 2 To lastSourceCell.Row
            If ws.Range("A" & i).Value = "Accessories" Then
                ws.Range("A" & i).EntireRow.Copy
                ThisWorkbook.Sheets("Sheet1").Range("A" & destinationRow).PasteSpecial xlPasteAll
                destinationRow = destinationRow + 1
            End If
        Next
        
        wb.Close
        MsgBox "Copy Complete"

    End If
    
End Sub

Function LastCell(ws As Worksheet) As Range

'
' Note "&" denotes a long value; "%" denotes an integer value
  
    Dim LastRow&, lastCol%

' Error-handling is here in case there is not any
' data in the worksheet

    On Error Resume Next

    With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

    End With

' Finally, initialize a Range object variable for
' the last populated row.
    
    Set LastCell = ws.Cells(LastRow&, lastCol%)


End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmac001Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.