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

Kill Command - Using Directory From Workbook Cell

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

Kill Worksheet Code

Nov 12, 2008

Kill Worksheet Code. I am using

View 4 Replies View Related

UN: Kill And Save As Not Working

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

Kill Avaya References

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

Variable Picture That Does NOT Kill VBA Times

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

Kill And Insert Event In Workbook_open

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

Kill Files With Specific Names

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

Amending The Filename In Cell("filename")

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

Press Command But And Execute The Code Assigned To Another Command Button

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

Shell - Adding Another Command To Forfiles Command?

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

Excel 2007 :: Using Search Command With IF Command

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

What Command Is Executing Or What Was Last Command Used

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

Get Date From The Filename

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

Extract Filename Using VBA

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

Add Date To Filename

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

Save The Filename

Sep 11, 2009

I have a problem with the following code

View 2 Replies View Related

Getting Date From Filename

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

Use ComboBox Value As Filename

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

Add The Date To A Filename

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

Filename As Cell Value

Oct 11, 2009

Is there a way to take filename and place value in a cell

View 9 Replies View Related

Filename Retriever

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

Get Filename With Formula

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

Extract Date From Filename For New Tab

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

Alternatives To CELL (filename)

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

Formula To Get The Date From Filename

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

Using Variable Filename In VLOOKUP

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

Filename = Cell Contents

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

Displaying A Filename In TextBox

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

Date Appended To Filename

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







Copyrights 2005-15 www.BigResource.com, All rights reserved