I have created a macro that saves a file then emails it out through lotus notes..
The problem is that how to i kill/delete the file that i have just made without it prompting me whether i want to or not? Or is there a way of only saving the file temporarily untill the macro has finishing running then it deletes the temporary file?
I have a macro at work that shows which files are duplicates (same name plus file size) it then produces a list of this with 3 fields file path(excluding file), file name and ext, and size. it produces a duplicate of the file for example:
file a
file a
file b
file b
i would like a macro to delete just 1 of theese (as deleteing both would be bad!! haha) and have no idea how to start this. other than going through all 1679 files that have a duplicate.
I have a CSV file with the following columns:
column1, Column2, Column3
A, B, "C,D"
I need to write a macro to :
1. Delete column2
2. Save the CSV file. At the time of saving I need to supress all default messages.
I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.
Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]
Private Sub CommandButton1_Click()
If Range("B6").Value = "" Or _
Range("d6").Value = "" Or _
Range("f6").Value = "" Or _
Range("E9").Value = "" Or _
[Code] ......
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies View RelatedI do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.
Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.
I have a list of numbers and I have corresponding text files which I named by using these numbers as reference
is there a possible macro to delete those rows which have corresponding text files by checking in a specific folder if that file name exists
I have created an AddIn with code for menu buttons. When I delete the .xlb file it is replace with one about 12 KB. After some use it starts growing. One station had it to 1 meg. Could this increasing file cause problems for the code? If so, is there a way to keep it from growing?
View 5 Replies View RelatedVB Code to delete a file. I have the following code that creates a file.
View 4 Replies View RelatedI was given a file to edit. This file was created with a theme "Classic Office 2" using Arial fonts. I cannot copy and insert new lines into the file. How do I edit the theme or delete this theme in order to get an editable file?
View 1 Replies View RelatedI am writing a macro that copies a pdf from a temporary directory to a permanent directory. After the copy is complete, I want to delete the original file. Looking in Visual Basic Help, it appears that I need to create a FileSystemObject, but I have no idea what that means or how to do it. sDirectory is a string variable of the path of the file and sBatch is a string variable of the name of file. This isn't working.
Set fs = CreateObject("Scripting.FileSystemObject")
sDeleteFile = (sDirectory & sBatch)
Set a = fs.CreateTextFile(sDeleteFile, True)
a.DeleteFile.sDeleteFile
I need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8
B2 to E9
C2 to F10
D2 to G11
E2 to H12
F2 to I13
I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
View 9 Replies View RelatedI am receiving an error at
Set CopyRng = Wkb.worksheet1.Range(Cells(RowofCopyworksheet, 1),
Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
I am trying to copy the first sheet in each file in the designated folder and paste it into a master worksheet.
Below is the code.
'Description: Combines all files in a specific folder to Format File for Upload.xls
Sub MergeMultipleFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer
[Code] ....
I presently have a macro that, when run, takes to conents of C4 and C6 and saves a new version of the file being worked on into a folder on my desktop. I love the macro with the exception of one part: I don't want to be prompted to overwrite the file if it already exists. How can I change this macro so that, when pressed, it overwrites the file without prompting the user and waiting for their answer?
Here is the current
Sub SaveIt() ...
When it opens the desired file, it increments the file name by 1 each time it is opened (via the macro).
Example;
The first time it is opened you see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages1"...
I close the file and open it again (via the macro), and see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages2"...
The file name continues to increment on every opening until the PC is rebooted, then it starts at 1 again.
Sub OpnLTpages()
Dim wb As Workbook
Dim AlreadyOpen As Boolean
AlreadyOpen = False
For Each wb In Workbooks 'Scan open workbooks
If wb.Name = "P'Binder L&T Pages.xls" Then ........................
I'm writing a macro in excel that edits a text file runs another program that uses this text file as its input and then stores the results. Perameters are then changed in the input file and it is then repeted over and over again. One of the parts I am stuck on is I need to delete the last 5 lines in the input file called INPfile.txt. See below:............
This section is always at the end of the file, is 5 lines long and always starts with [DEMANDS] and always ends with [END].
I have posted already the problem to delete the code when sending a sheet via email and I have found the solution to the problem.
I tried to do the same when saving the file to specific folder, but i am facing problem.
Is there anyway in which I can open a spreadsheet and then delete any macros which exist in that file with VBA?
For Example
Workbooks.Open "\sysdevwebwCorpAcctSuiteReportsNetProfit.xls"
''Delete Any Existing Macros Code''
Activeworkbook.Save
Activeworkbook.Close
we work in a group on different excel worksheets. now we want to combine this sheets and get the following linkages in the files:
= 'I:[versuch_joe.xls]Koeffizient'!C156+'I:[versuch_joe.xls]Koeffizient'!C157*B25+'I:[versuch_joe.xls]Koeffizient'!C158*(LN('I:[versuch_joe.xls]Daten'!Y17)-LN('I:[versuch_joe.xls]Daten'!Y16))+'I:[versuch_joe.xls]Koeffizient'!C159*'I:[versuch_joe.xls]Daten'!R17+'I:[versuch_joe.xls]Koeffizient'!C160*('I:[versuch_joe.xls]Daten'!AE16+'I:[versuch_joe.xls]Daten'!AE15)
how can I delete all "[versuch_joe.xls]" that are not needed anymore? i got hundreds of them in my file.
I use the Append command to write a line to a text file. I have about 3 excel files appending data to the same text file.
If someone were to write a line in error I would like to have the ability to delete it.
I have a huge data file. I would like to have excel automatically delete all rows with the value of 7 in column b. Can I do this without manually selecting all of the rows (I can sort by that value, but there are 120,000 rows).
View 6 Replies View RelatedI have a large *.tab file with around 450,000 records. I opened the file in excel and then filtered two columns where there are values equal to 0.
I then highlight all the rows and try to delete them, but I receive the following error popup box:
"Microsoft Excel cannot create or use the data range reference because it is too complex."
How can I delete all these filtered = 0 rows without getting this error??
I am trying to read a text file into a variable with VBA. The files I try to read usually have a bunch of unrecognized characters in them and I can't seem to read through them. I am looking for some code that will delete all unrecognized characters until it finds a string I specify.
Example of file
ΚW
¾J Y D Y ³F Y ZE Y ¨B Z ˜6 Z
GOOD DATA
My code works fine if I manually delete all this stuff before GOOD DATA, but wont work if I dont. My "responseposition" is always 0 unless I delete all the nonsense.
Sub findvalues()
Dim strText As String
strText = GetFileContent("C:currentday.gdbm")
cardstring = "GOOD DATA"
responsePosition = InStr(1, strText, cardstring, vbTextCompare)
MsgBox (responsePosition)
End Sub
I already have code that I believe is supposed to do what I'm trying to accomplish. However, it doesn't fully work. The Macro is below:
VB:
Workbooks.Open Filename:="C:Documents and Settingsplp138DesktopExample 1.xls"
On Error Resume Next
With Workbooks("Example 2.xls")[code]....
Basically, what I'm trying to accomplish is to have a certain range from one file copied, and pasted in the next available (empty) row of another file. While debugging I see that everything is working except for the actual pasting.