VBA Copy/Paste with variable file names

dwool40

New Member
Joined
Apr 27, 2018
Messages
27
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,950
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
 

MUKESHY12390

Well-known Member
Joined
Sep 18, 2012
Messages
892
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
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
 

dwool40

New Member
Joined
Apr 27, 2018
Messages
27
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.
 

dwool40

New Member
Joined
Apr 27, 2018
Messages
27
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!
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,320
Members
425,273
Latest member
tonio909

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
Top