VBA Copy/Paste with variable file names

dwool40

New Member
Joined
Apr 27, 2018
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I am using the following code to copy/paste.

VBA Code:
Sub Copynotice()
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("D:\Downloads\DataGridExport.xlsx")
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D3").PasteSpecial Paste:=xlPasteValues
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

The problem is, the downloads folder must be emptied before each download because each successive download adds a (#) to the filename. So, DataGridExport.xlsx becomes DataGridExport(1).xlsx then (2) and so on. Is there a way to reference the most recent DataGridExport.xlsx ignoring the (#)?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
If your download folder contains only your DataGridExport files then this code will open and use the most recently saved file. HTH. Dave
Code:
Function NewestFile(FlderName As String) As String
Dim objfso As Object, objfolder As Object, objfile As Object
Dim NewFileName As String, NewFileDate As String, Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder(FlderName)
NewFileDate = vbNullString
For Each objfile In objfolder.Files
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Path 'Name
End If
Next objfile
'MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
NewestFile = NewFileName
Set objfolder = Nothing
Set objfso = Nothing
End Function

Sub Copynotice()
Dim wb1 As Workbook, wb2 As Workbook, FlStr As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
FlStr = NewestFile("D:\Downloads")
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(FlStr)
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D3").PasteSpecial Paste:=xlPasteValues
wb2.Close Savechanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
 
Upvote 0
may be this...

VBA Code:
Sub Copynotice()
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook

'To delete existing
on error resume next ''' ignore error if there is no file
Kill "D:\Downloads\DataGridExport.xlsx"
on error goto 0  ''' clear all error

Set wb2 = Workbooks.Open("D:\Downloads\DataGridExport.xlsx")
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D3").PasteSpecial Paste:=xlPasteValues
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
 
Upvote 0
If your download folder contains only your DataGridExport files then this code will open and use the most recently saved file. HTH. Dave
Code:
Function NewestFile(FlderName As String) As String
Dim objfso As Object, objfolder As Object, objfile As Object
Dim NewFileName As String, NewFileDate As String, Temp As String
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder(FlderName)
NewFileDate = vbNullString
For Each objfile In objfolder.Files
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Path 'Name
End If
Next objfile
'MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
NewestFile = NewFileName
Set objfolder = Nothing
Set objfso = Nothing
End Function

Sub Copynotice()
Dim wb1 As Workbook, wb2 As Workbook, FlStr As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
FlStr = NewestFile("D:\Downloads")
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(FlStr)
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D3").PasteSpecial Paste:=xlPasteValues
wb2.Close Savechanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Unfortunately, there can be many files in this folder at any given time.
 
Upvote 0
may be this...

VBA Code:
Sub Copynotice()
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook

'To delete existing
on error resume next ''' ignore error if there is no file
Kill "D:\Downloads\DataGridExport.xlsx"
on error goto 0  ''' clear all error

Set wb2 = Workbooks.Open("D:\Downloads\DataGridExport.xlsx")
wb2.Sheets("Report1").Range("E2:E120").Copy
wb1.Sheets("Notice").Range("D3").PasteSpecial Paste:=xlPasteValues
wb2.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

I was able to use:
VBA Code:
Sub Downloads()
On Error Resume Next
Kill "D:\Downloads\*.*"
On Error GoTo 0
End Sub

I just created a button to click before they download. It empties the folder of all files. Perfect...Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top