Open & delete file using a macro

[Dode]

Hi,

I recieve an excel file daily which has the date attached to the end of the
file name - ie Agent_SCCS12042007. How can I amend my script below to :
(1) have the macro open the file & import the data regardless of the date
information
(2) then delete this file from the d:drive

thanks!


ChDir "D:\UserData\userid\My Documents\Daily Telephony\new extracts to update"
Workbooks.Open Filename:="D:\UserData\userid\My Documents\Daily
Telephony\new extracts to update\Agent_SCCS"
ActiveWindow.SmallScroll ToRight:=10
Range("Q1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("Q1:AC391").Select
Selection.Copy
Windows("Macro for scrubbing extract data.xls").Activate
Sheets("AGENT_SCCS").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Agent_SCCS.csv").Activate
ActiveWindow.Close

 

[Dave Peterson]

You can drop the ChDir line. It's not necessary.

Workbooks.Open _
Filename:="D:\UserData\userid\My Documents\Daily Telephony" _
& "\new extracts to update\Agent_SCCS" & format(date,"mmddyyyy") _
& ".xls"

(If the extension is .xls)

 

[Dode]

Thank you for the info...
When I ran the macro it came up with the run-time error '1004'. This is due
to the fact that the file name has the time it was pulled after the date. My
mistake - sorry.
Therefore, I adjusted your line to read
Filename:="D:\UserData\userid\My Documents\Daily Telephony" _
& "\new extracts to update\Agent_SCCS" & format(date,"mmddyyyy") &
format(Time, "hhmm") & ".csv"

but I still run into a problem because the data is pulled early morning and
not always at the same time. Hence, the macro above is looking for today's
file with the current time when in fact file could be Agent_SCCS120520070550.
Can you offer any suggestions?

Thanks

 

[Dave Peterson]

So there's only one .csv file per day? And it's always today's date?

If yes to both:

Dim myFileName as string
dim myPath as string
mypath = "D:\UserData\userid\My Documents\Daily Telephony" _
& "\new extracts to update\"
myfilename = dir("Agent_SCCS" & format(date,"mmddyyyy") & "*.csv")

if myfilename = "" then
msgbox "No file with today's date!
exit sub
end if

workbooks.open filename:=mypath & myfilename

....