Kill Filename Command
Jan 17, 2010
I've got a sheet that I need to convert certain rows into CSV for a payroll package. I'm using excel 2003 on XP.
I have worked out a macro to save it as a csv file with a different name. The only way I managed to do this was by creating an xls file with the data I wanted and then saving it as a csv file with a different name. But now I need to delete the intermediate xls file (called pre CSV in this example). I have the following code which seemed to work first time but is now giving me the error "Runtime error 53 - File not found" and highlights the. What am I doing wrong?
Sub Direct() ....
View 9 Replies
ADVERTISEMENT
Feb 15, 2010
I want to use the following kill command to delete all files in a folder:
Kill "C:dsmanagerest*.*"
However, I want the directory to be specified not within the code, but within a cell on a worksheet. Ie. Cell A1 would contain the directory C:dsmanager est*.*"
View 6 Replies
View Related
Nov 12, 2008
Kill Worksheet Code. I am using
View 4 Replies
View Related
Sep 3, 2009
I hate to post this again, but I still cannot figure this out and I haven't gotten any response. I need help folks.
The code below forces a new file to download each time the workbook TEST_CASE_1.xls is opened. I can't seem to get it to kill the _temp.xls file or save the new file (without being prompted "Do you want save"). Any ideas as to what I am doing wrong?
View 7 Replies
View Related
Aug 14, 2007
The following code accesses the external aplpication 'Avaya CMS Supervisor' and loops through a worksheet full of data pulling reports. Everthing works great. After running this code there is nothing in Task Manager that is Avaya related, however when I try to physically open/login to the application it hangs at logging in.
In order to get physically logged in, you must shut Excel completely down. That's why I don't think this is Avaya related, but more something in Excel.
Sub AvayaExportServer1()
'These correspond to menu:Tools References associated with Avaya
Dim cvsApp As New cvsApplication
Dim cvsConn As New cvsConnection
Dim cvsSrv As New cvsServer
Dim cvsCatalog As New cvsCatalog
Dim cvsRpt As New cvsReport
'Used in the For/Loop to increase rows by 1 from list of Avaya inputs on worksheet
Dim x As Integer
Worksheets("Server1").Activate
View 8 Replies
View Related
Sep 11, 2012
I have a template that needs to show one of 4 logos. The logo shown is the ONLY difference between the four models (A, B, C & D). My life would be much simpler if the template includes all four logos, and a switch controlled the one logo they want to see on each model. But the way I first programmed it absolutely KILLED my processing times for the model's many VBA macros.
What's the best way to accomplish this objective without sacrificing my VBA?
View 2 Replies
View Related
Sep 22, 2006
Private Sub Workbook_Open()
If ActiveWorkbook. Name = "UserA.xls" Then
Call PreviewList
Else
Cancel = True
End If
End Sub
After the macro auto-startup, UserA works on the worksheet and input some procedures and save as ClientA.xls. However, before save as ClientA.xls and close, UserA wants to change the worksheet name and Procedure name as below, how to do that?
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "ClientA.xls" Then
Call RevisedList
Else
Cancel = True
End If
End Sub
View 2 Replies
View Related
Mar 9, 2007
I have a directory that contain a large number of files. How do i preserve certain files with specific filenames while other files are deleted?
For example,
I have files that are named : "58-26150B", "58-26200", "58-26200B", "58-26500B", "58-26550" etc. Please refer to uploaded screenshot.
The files that i want to keep from the above are "58-26200", "58-26200B". The code should be able to loop through the entire directory & preserve only specific files while deleting the rest.
Private Sub CommandButton18_Click()
Dim nWave As Integer, i As Integer
Dim ConvergedFile As String, Pattern As String, KillFilePath As String, SimilarFile As String
nWave = Worksheets("nomogram").Cells(13, 4)
For i = 1 To nWave
ConvergedFile = CStr(Worksheets("nomogram").Cells(33, 3 + i)) 'Loop through list of wanted file names "58-26150", "60-25950" etc
Pattern = Mid(Trim(ConvergedFile), 1, 3) 'Update the filename prefixes i.e. "58-", "60-" etc
View 6 Replies
View Related
Jul 7, 2006
I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?
View 3 Replies
View Related
Apr 14, 2009
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies
View Related
Apr 19, 2014
I am playing around with the Forfiles command (being called from Excel via Shell)..
I can't for the life of me get it apply a second command (such as getting the file size of each file)..
For example:
VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d
[Code]....
The end result being the filename and the filesize shown..
View 4 Replies
View Related
Jun 20, 2013
Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
View 2 Replies
View Related
Feb 17, 2010
Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?
On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand
Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...
AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.
Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.
I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack
View 9 Replies
View Related
Jun 18, 2014
I have a filename 'filename-12-14-2014' where i need to extract date information from the filename and insert a new heading in the file 'date' in the first column and write the extracted date till the end (until data exists). I can write the new column name but i could not write date in all the fields. code i wrote is below
this is for extracting date from the filename. the 4th lines throws me the error?
(1) LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
(2) With Range("A2:A" & LastRow)
(3) x = Split(s, "- ", 2)
(4) .Value = Left(Mid(x(1), InStr(x(1), "-") + 1), InStr(Mid(x(1), InStr(x(1), "-") + 1), ".") - 1)
(5) .NumberFormat = "mm/dd/yy"
for adding the date in the first column. This works fine
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
s = ActiveWorkbook.Name
Range("A2").Select
View 7 Replies
View Related
Jul 21, 2014
I have excelfilename starts like abc - usa sales- 1-1-2014. have to extract usa from the filename and insert as a column in excel with columnname 'country'.
View 1 Replies
View Related
Jun 3, 2009
I picked up a bit of code to create a CSV with a | delimiter. so that I could save the file with a date added onto the filename. i.e. myfile060309.csv.
View 4 Replies
View Related
Sep 11, 2009
I have a problem with the following code
View 2 Replies
View Related
Nov 13, 2013
I have filename as filenamex - filenamey-11-13-2013. I want to get only the date from the filename.
View 3 Replies
View Related
Feb 4, 2008
I'm trying to use the value of a combo box as the filename when saving as. The code i have so far is:
Dim fName As String
fName = FileNameTXBX.Value
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlCSV, _
CreateBackup:=False
View 9 Replies
View Related
Aug 28, 2009
I need to add a date to the existing filename it is being saved as. Currenntly the filename is saved as follows:
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
y = Cells(a, 12)
Workbooks.Add ("C:Documents and SettingsDesktopTemplate.xlsx")
ActiveWorkbook.SaveAs FileName:=y
Next a
I want the filename to stay the same as above but add "-r-"a number entered into a textbox representing the revision and then the date the file was saved in the following format dd/mm/yy.
The form containing the text box has been scripted and works fine. The string entered in the textbox currently resides in the Active sheet being saved in cell "E4".
Below is a completed Filename. Note the RED is what i need to add.
2138-035-PW-COM01-RCS-001-r-1-28/Aug/09
View 9 Replies
View Related
Oct 11, 2009
Is there a way to take filename and place value in a cell
View 9 Replies
View Related
Jun 28, 2006
I've made a simple filename retriever. Click on the button to list the filenames under the specified path. I would like it to go deeper into subfolders. At the moment it only drills down one level. Need to make it able to drill down to all the levels present and list out the filenames.
View 4 Replies
View Related
Aug 7, 2008
I am trying to use the formula below to get the filename of the spreadsheet, it seems to work fine but when you open a second sheet the first sheet will pick up the name of the second. =MID(CELL("filename"), FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1). These spreadsheets will then be uploaded to another application and having 2 with the same name could cause issues. Obviously the answer is to only open one at a time, but this is always subject to human error!
View 3 Replies
View Related
May 9, 2014
Every week we produce a report that has the filename: Master_BER_Pending Revokes Report_YYYY-MM-DD.xlsm
Some of the information from this workbook needs to be pasted into a new tab on a different workbook. The new tabs naming convention is simply "DD MM". Currently I've got a command prop that asks the user to enter it in manually. See below
VB:
Dim newsh As String
newsh = InputBox(Prompt:="Enter new tab name", Title:="Tab Name", Default:="e.g. '05 05'")
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = newsh
The user gets the information for the month and day from the filename of the report that was produced. What I need is the DD and MM to be extracted from the filename and used to name the new sheet.
View 6 Replies
View Related
Apr 7, 2009
CELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.
What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?
View 2 Replies
View Related
Jun 25, 2009
I have a folder full of sales reports, all of the files are given a consistant format e.g. 09_wk50. I have a macro that do various calculations in each book. I now need a calculation based on the report date.
I am able to get the file name in the sheet so that I am just left with the value above (09_wk50) but I need to be able to get an actual date from that value.
I need to be able to get the monday of the week given. for the example given i would want the date 13/04/2009 because of week 50 in 2009, the 13th is the Monday.
View 4 Replies
View Related
Mar 26, 2014
I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.
This is the original code where it asks for the user to select the sheet every time.
[Code].....
This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):
[Code] ...........
I tried to enter the Full Costs sheet name into the code like so:
[Code] ......
However this produces the following:
[Code] .....
The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.
View 3 Replies
View Related
Dec 11, 2008
I would like to modify the below so that the contents of cell D2 of the current file becomes the file name.
View 2 Replies
View Related
Apr 16, 2009
I want to use this code to display only the filename + extension in the TextBox1. It currently shows the full path
View 8 Replies
View Related
Oct 29, 2009
I searched this forum to find a macro that will do what I'm describing below. There seems to be quite a number of macros doing almost what i want, but most require a macro to be run, filename to be entered, etc...
I want the macro to automatically append the current date to the filename when it is saved. It must save it in the same directory where the file was opened from, and the name must be exactly the same as before, except that it must remove the previous date appended to the filename, if there was one appended.
I don't want to run the macro for this to happen, the normal File Save command must do the trick...
Thus c:my documentsTest.xls will become c:my documentsTest 29102009.xls; and
c:my documentsTest27102009.xls will become c:my documentsTest 29102009.xls
View 9 Replies
View Related
