Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open & delete file using a macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open & delete file using a macro
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open & delete file using a macro
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open & delete file using a macro
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 .... Dode wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to run on file open | Excel Discussion (Misc queries) | |||
Open File within a macro | Excel Discussion (Misc queries) | |||
Can't find macro in an Excel file to delete it | Excel Discussion (Misc queries) | |||
Delete/Close Excel file that is kept OPEN | New Users to Excel | |||
open file (as variable) from macro | Excel Discussion (Misc queries) |