Ignore File Name / Save File Name Different If Other Exists?
Jun 6, 2014
My macro pulls data from a set of files in a folder and creates an output based on my formatting rules.
Ran into 2 issues though. The first being is after this macro is run it saves a file in the folder with the name "Data Export" and before I had it do this if "Data Export" was already there
Code:
'Check if file name to save exists If Dir$(vFolder & "" & sSubFolder & ".xls", vbNormal) = "" Then
wb.SaveAs vFolder & "" & sSubFolder & ".xls"
MsgBox "Complete!", vbOKOnly
Else
MsgBox "File already exists, could not save!", vbInformation, "COMPLETE!"
End If
What I want it to do instead is change itself and save it as Data Export A, and then also loop for A each time, if A is there go to B , so on and so forth.
-Alternate solution if this is way to challenging, can I have it pop up the Save Message box to let the user try a new name if "Data Export" exists. I was trying to think through that too..
2nd Issue is when I loop the files in the folder I selected in my macro, I want it to ignore "Data Export" files. Before I just took a shortcut and had it save outside of the data folder, which was fine but now we are running on so many files a day that we need it inside the same folder for organization purposes.
That is done with the following
Code:
'Loop through files in folder sFileName = Dir$(vFolder & "")
Do Until sFileName = ""
I was thinking maybe I can add an error handler to the loop? Before Do Until? Something like this? But how I can add it into the loop?
Code:
If sFileName Like "????.?.xls" Then
View 4 Replies
ADVERTISEMENT
Sep 22, 2009
I'm having a slight problem with this script. What I'm looking to do is before the workbook is closed is check to see if the workbook already exisits on the users desktop if it doesnt then save it to the users desktop. if is does exists then just exit the sub. Here is what I have so far but for some reason it kept displaying the aleart message that the file already exists and wants to save it again.
I just added the displayalerts = false in but the script still does not know that file already exists. So when the file is closed it keeps saying file has been placed on your desktop.
View 3 Replies
View Related
Oct 10, 2011
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies
View Related
Jul 6, 2012
I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub
View 1 Replies
View Related
Jun 4, 2009
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
View 9 Replies
View Related
Apr 9, 2014
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] ......
View 1 Replies
View Related
Jan 27, 2014
A co-worker of mine is exporting files from Quickpen as excel files, but every time he names the files with any Uppercase letters in the files name, they are automatically changing to all lower case. Even when he tries to rename the files, they will not stay with any uppercase letters....I have searched all over and cannot find a solution. If he sends ME the file, I can open it in excel and save it with any cases.
View 2 Replies
View Related
Jul 10, 2014
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
View 1 Replies
View Related
Mar 14, 2014
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'
View 2 Replies
View Related
Oct 19, 2005
How can I set the SAVE AS file name to equal A1's cell contents? (More specifically, when I need to rename an existing open file and place my cursor in A1 and hit Save As, I need to new file name to automatically populate A1's cell contents so I don't have to retype the contents of A1.)
View 1 Replies
View Related
Mar 12, 2014
How can I save worksheet from existing workbook as a new workbook with extension .xls or .xlsx depending on the version of Excel on which the original was opened and no matter the extension of original?
My code that I was trying to create all above does not work, because even if I am using Excel 2007, 2010 or 2013 it will still be saving only as .xls.
View 2 Replies
View Related
Mar 6, 2012
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 Related
Dec 20, 2009
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................
View 2 Replies
View Related
Oct 11, 2006
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() ...
View 3 Replies
View Related
Aug 3, 2014
There are two excel files abc.xlsm and xyz.xlsm enclosed. download these two excel files to your desktop. Then open abc.xlsm and run following code.
[Code]....
After running code above you will see that you have a new file in your desktop named xyz.rar. Everyhing is okey until now.
Now, delete abc.xlsm, xyz.xlsm, xyz.rar from your desktop. Then download enclosed file named klm.xlsm to your desktop. Then open klm.xlsm and run following code.
[Code] ....
After running code above why there is no klm.rar file in your desktop? Is it not possible to create a zip file running code includes same workbook? I mean why I cant zip excel file, from same excel file?
Attached Files :
klm.xlsm
xyz.xlsm
abc.xlsm
View 1 Replies
View Related
Aug 27, 2009
What I would like to do is to open an excel workbook from a bat file in dos with one arguement.
so something like
start c:lacklist.xls 2315t
Once the blacklist.xls is opened I would like to have the macro to save the excel file as a csv file ignoring all the prompts and use the argurement 2315t as the directory to save it to on the j drive ie save to j:2315tlacklist.csv. Then close the workbook and continue with the rest of the commands in the bat file.
I'm guessing I have to use GetCommandLineA to read in the arguement but I can't find anything on the net that gives a clear explanation of how to use it.
View 3 Replies
View Related
Aug 22, 2014
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
View 2 Replies
View Related
Nov 8, 2007
I have an excel spreadsheet used to populate word documents based on a template file that is loaded like this....
View 9 Replies
View Related
Sep 7, 2009
Through excel I run a dos program which spits out a results file and my macro then opens this file in excel. The only problem is the macro tries to open the file before the other program has writen it. I have tried to fix this using the
View 5 Replies
View Related
Oct 2, 2012
How can I check if a file exists with VBA?
View 3 Replies
View Related
Apr 21, 2013
I would like to have a user designed function that will tell me if a file exists in the specified directory.
For example, my file name is wd1.xlsm. I have another udf that tells me my current file path - e.g. C:All Sales People Quotes System.
I combine these two in say, cell D4 and so have C:All Sales People Quotes Systemwd1.xlms
Now I would like a udf that I can put in E4 that will tell me if wd1.xlsm exists in the C:All Sales People Quotes System directory.
View 4 Replies
View Related
Nov 28, 2009
a macro to check if a file exists before saving. The code saves the file OK if the filename does not exist but fails if it does.
Sub CheckFileName()
Dim FName As String
FName = ThisWorkbook. Name
'saves file
Dim FPath As String
FPath = ThisWorkbook.Path & ""
Dim y As String
View 9 Replies
View Related
Sep 15, 2009
Is there a way to test (from Excel 2002) whether a specified file (not necessarily an Excel file) exists on my computer? I could specify the complete path.
View 4 Replies
View Related
Nov 14, 2008
I am using the following VB code to check if a file exists and if so add a formula in my workbook providing data from that file.
I have pre-pulled raw data in the directory "H:BusinessRptTest for scripts" which is named firstly by the day of the month and then what data it is.
- i.e/ 13_CAI_AgentStats.xls
The idea of my code is to check firstly whether the file exists (i.e has the data been pulled yet) and then if so to extract the data I need from the correct sheet and if not to add a zero instead.
I thought all was ok, but if the raw workbook is not open I get a #REF error.
I have looked at some posts for getting info from a closed workbook on this site but I can't get my head around incorporating them into my code!
Code listed below
Sub Check_File_Exists()
For date_test = 1 To 31
If Dir("H:BusinessRptTest for scripts" & date_test & "_CAI_AgentStats.xls") "" Then
Range("AE" & date_test + 11).Formula = "='H:BusinessRptTest for scripts[" & date_test & "_CAI_AgentStats.xls]" & date_test & "_CAI_AgentStats'!$D$4"
Else
View 9 Replies
View Related
May 2, 2006
I can use a FileDialog to have a file selected. But I still need to check if it really exists before continuing. How to do?
(I know there is a function FileExists but do not know how to use it with FileDialog if this is the way to do it)
View 9 Replies
View Related
Aug 13, 2006
Select Case True
Case True
If wbEXT = "C:KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1)) >= 0 And _
wbINT = "C:KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1)) >= 0 Then
Case False
varAnswer = MsgBox("KPI Current week No missing", vbOK, "Warning")
If varAnswer = vbOK Then
Exit Sub
End If
End If
End Select
rest of code here
basically if current week files exist carry on , if they dont then pull up a msgbox with ok button to stop sub
View 5 Replies
View Related
Oct 11, 2006
Is it possible to search a directory looking for a particular file, then if found, copy the directory and all its contents to another location?
it should be noted that not all of the files will be *.xls.
View 3 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Nov 29, 2009
I have written a macro to select a range of cells one by one and open the excel files mentioned in them and execute a task. It works fine. But I want the system to see if the file exists in its folder or if the file is password protected (for opening). Either of the case the file should be skipped without a warning message and any attempt to open. I need a macro for that. Following is the way the cell contents are
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-LEGGINGS-13246.xls
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-PANTS-13247.xls
I:ORDERSS-S 10ALIYAMARKET ORDERSADDADD-LADIES-SHORTS-13244.xls
I:ORDERSS-S 10MICHELLEGAMESWORLDWIDE COMPANYWORLDWIDE COMPANY - GAMES - 10014.xls
And following is the VBA code I wrote. It it almost stops when reaching a non-existing file name
View 2 Replies
View Related
Jun 2, 2014
I have a workbook that links to four files. I would like to automatically update the links when the file is opened and if the linked files are not available, don't display a message.
I got the first part done using the Options menu in Excel but can't figure out how to suppress the message when the linked files are not available
View 3 Replies
View Related