Open Multiple Files, Check Current Month & Save To New Location
Sep 15, 2007
I have 40 files in one folder which I named it as "CA" + month's name that I am working on. I need to do analyse these files monthly and save it under new folder. how do I automatically save them in new folder and name them for that particular month. Also, each file has worksheet which has one cell as "Aug-07" and the cell next to it has number of that month that is "08". How do I automatically change this also based on the name of the file, because file name month and month in the cell are the same.
View 9 Replies
ADVERTISEMENT
May 6, 2014
I have this existing macro which saves each tab into a separate excel file. However, I'd like for it to also save them together in a new folder using the current month and date (named: QA Files May_05.06.14). The month and date would change according to current month/date. How would I incorporate that into this code? I am not good with macros.
Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("D1").Value
Next
[Code]...
View 4 Replies
View Related
May 4, 2006
I get an xl file sent to me every day which I upload into a database. I then save the file keeping its name and save it into a selected folder. Is it possible to add a macro at the beginning of my current macro that saves the file using its name to a selected folder?
View 4 Replies
View Related
Jul 14, 2014
Here is a macro I am using to save a workbook using 'Save As' .
I have it open a template workbook we use, and then perform a save as, pathed to a specific folder on the desktop. We do this every night just before midnight.
What I haven't been able to figure out is how to have it save to the next month folder (The folders with the name of all the months are already created) on the last day of each current month.
For example. In my code, the final folder in the path is the current month July. On July 31 when I run the macro, I would like it to save it in the folder called August.
Is there a way to code this so it will recognize what the current month is and then at the end of each current month save it in the next month?
[Code] .....
View 12 Replies
View Related
Feb 2, 2014
I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code:
Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet
[Code] ........
View 3 Replies
View Related
Nov 19, 2012
I am using xp pro, excel 2010 and the ftp server has linux installed. To open files from server I press open button in excel, then choose ftp location from drop down menu. FTP logon box asks password with the option of default user selected. I provide password and go on.
Some days ago something happened, I don't know what but as a result of it when I opened the FTP logon box, the anonymous option was selected by default. So I selected user option, gave id password and hit ok. It did not connect. Then I removed that ftp location from excel ftp locations. Whenever I tried to add an ftp location, excel restarted stating error message " *** encountered a problem *** ". So I reached a site after some googling and was directed to add the whole ftp file location path in the "File name" box of the open dialog box. It worked and the ftp location was automatically added in ftp locations. [ any ftp setting in excel is automatically transferred to ms word as well]
PROBLEM > Now I have a different problem. I can access ftp folders and subfolders but when I select a file and click open, downloading bar at the bottom of the excel does not start and after a long wait an error message pops up " the internet address ' ftp://192.168.****/folder/subfolder/filename.xls" is not valid. [I can open ftp files using filezilla etc]
View 2 Replies
View Related
Jan 16, 2010
I am attempting to write an Excel macro that will be stored in a file called MacroFile. The purpose of the macro is to
1. Follow a hyperlink to an Excel file saved in a SharePoint type enviroment
2. Save the file to my laptop directory My Documents.
Below is the code I have written. The code is following the hyperlink and saving a file but is the focus file is incorrect.
Here is what happens:
1. Open up MacroFile and run macro
2. Hyperlinked file LinkedFile_1.xls is opened
3. File NewFile_1 is saved but contains the info from MacroFile
4. Hyperlinked file LinkedFile_2.xls is opened
5. File NewFile_2 is saved but contains the info from LinkedFile_1
6. Hyperlinked file LinkedFile_3.xls is opened
7. File NewFile_3 is saved but contains the info from LinkedFile_2
The files created are named correctly but have the wrong data in them. I need to know how to control which file is considered ActiveWorkbook.
Sub LinkAndCopy()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
'**** Copy LinkedFile_1..................
View 2 Replies
View Related
May 30, 2008
I have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook.
The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls"
The months and years will need to change.
The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
View 9 Replies
View Related
Nov 26, 2012
I just got a new computer and upgraded to Excel 2010 and Windows 7. When I try to open a workbook in Excel 2010, my saved file paths on the left side of the screen are gone. I want to put them back in there.
In Excel 2007 running Windows XP, I would just right click in the left side menu and click "add" and now that's no longer an option.
View 1 Replies
View Related
May 28, 2008
I am trying to allow the Command Button when clicked to go through multiple conditions before making a decision. So, when someone clicks on Command Button 3 the code should look to see if CheckBox1 is true, then it should check today's date, and if it is between a range of days, or even months, then it would add the number in TextBox1 with the amount already in cell H18. This event will happen every time someone clicks on the Command Button.
The end result is to have several sheets (4 total) for each quarter in the fiscal year, and if the dates are within those parameters, the clicking of the command button will update the correct sheet.
View 9 Replies
View Related
Apr 3, 2014
In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.
However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?
e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.
View 2 Replies
View Related
Apr 21, 2009
I have the code below. It openes all csv files within a folder, then delete rows depending on the value of a cell. Now it needs to save the file as a xls file ....
View 9 Replies
View Related
Jul 9, 2012
I have files stored at C:KLQ1. Some with .xls and some with .xlsm. Now I want to same all the files under C:KLQ2 with .xlsm extension. Then replace all the 2005 with 2006 in the saved-as file. Then close all the files in Q1 without saving and all the files in Q2 with saving. The following code is modified from somewhere but does not work.
Code:
Sub OpenCloseFile()
Dim i As Integer
Dim wbResults As Workbook
Dim ws As Worksheet
[Code]....
View 7 Replies
View Related
May 22, 2006
i've got the code below, and if you notice the directory and file line, I want a loop that'll do what the macro below does, but also for files j1k2-j1k200. I'm hoping there's a macro that'll be able to do this, as I don't want to have to copy, paste and edit the same macro below 200 times!
Application. ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("C:Uni StuffStocks_J20j1ka.xls", False, False)
Sheets("Sheet1").Select
Range("B6:S6").Select
Selection.Replace What:="]w1", Replacement:="]w2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
wb.Close True ' close the source workbook saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
View 5 Replies
View Related
Aug 25, 2006
I am trying to use a shell command to dig into a specified directory (and ideally all sub-directories) and open every pdf file and re-save with security settings changed (adding a password to prevent printing). I can't seem to get the shell command to run from where it is:
Option Explicit
Sub OpenPDFFilesAndSave()
'opens every pdf file in a directory
Dim FSO As Object, Fld As Object, Fil As Object
Dim MainFolderName As String, i As Integer
Dim RetVal As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
MainFolderName = "C:Test"
Set Fld = FSO.GetFolder(MainFolderName)
For Each Fil In Fld.Files
i = i + 1
RetVal = Shell("C:Program FilesAdobeAcrobat 7.0
_&AcrobatAcrobat.exe MainFolderName & " " & Fil. Name")
Next
'have to add save the file, and change the print settings but not figured that out yet
End Sub
View 3 Replies
View Related
Dec 21, 2007
I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit
12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit
View 9 Replies
View Related
Oct 11, 2011
Is there a way to convert all files in folder, in this case, xml in excel format to xls without open them?
I recorded the macro below, but this needs to "Open" and "SaveAs" the current file in folder and there are a few thousands of xml files in folder.
I was wondering if through some ADO or VBA code this can be done without open the files in order to save resources and get the work done faster?
Code:
Sub Convert_XML_to_XLS()
Workbooks.Open Filename:="C:MyPathInputFile.xml"
ActiveWorkbook.SaveAs Filename:="C:MyPathInputFile.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False '
End Sub
View 3 Replies
View Related
Nov 11, 2013
There is a folder with all excel files with the same structure. I need a macro, who opens one file by one in a folder, change the layout, and save it too same place with same name. Changing the layout will I do with macro record.
Sub AllFiles()
Dim MyFolder As String 'Path containing the files for looping
Dim MyFile As String 'Filename obtained by Dir function
Dim MyBook As Workbook
MyFolder = "D:LABODIESTSOST_DIEST" 'Assign directory to MyFolder variable
[Code] ......
View 9 Replies
View Related
Sep 15, 2014
I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer
[Code] .....
View 4 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 Replies
View Related
May 21, 2008
I have a number of spreadsheets in a folder called country (each sheet is the name of a region). These sheets are linked to another sheet called master which is in another folder.
The regional sheets all have a list of wrap codes and pull details specific to each code from the master sheet. All wrap codes for all sheets are stored in the master, details for wrpas are manually entered here as well as any other associated information.
The link is kept by way of an index match formula used to populate information from the master for all wrap codes in the regional sheet.
Each of the regional sheets act as a report for a specific region and the master is the main source. Every week we need to open the regional sheets (some reside in different folders/subfolders) update the index match formulas in the sheet so that the data is refreshed and then save as a new file with data as values to another folder for reporting.
I am looking for a code that can automatically update all of the excel sheets in a given folder and then save them to a new folder automatically.
I have very very limited knowledge of VB (did make a hello world dialog box once) and am stuck as to any way to automate this process.
View 5 Replies
View Related
Jul 3, 2007
I'm doing the following:
Dim myname As String
myname = Application. GetOpenFilename
I use it to attempt to open first a QuickBooks file and then an excel workbook.
The Quickbooks file opens fine. The Excel workbook never appears to open but
I do get the full file pathname to my excel workbook returned which I then
parse off to get just the workbook name.
Why would it behave this way? I open both these files manually all the time.
Also, I'm trying to use a filter as follows:
myname = Application.GetOpenFilename("*.QBW")
I get a compile error saying that I'm calling the function wrong.
View 5 Replies
View Related
Oct 24, 2007
I will have about 20 excel spreadsheets that will need to be run through some data cleansing and validation than each one exported to a csv file (without the column headings on them), and saved as the same file name as the spreadsheet was. If there are errors in the validation process then the one that fails (row) will be copied to an error log spreadsheet. There will be multiple worksheets in the error log workbook (one for each of the spreadsheets – which I hope VBA can create). What I am doing so far is creating a loop that will run on all of the spreadsheets located within a folder.
'Procucedure that will run all validation processes and error checking on extracteds spreadsheets
Sub RunCodeOnAllXLSFiles()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch..............................
View 3 Replies
View Related
May 8, 2007
i have this code below that opens a prn file. What i want it to do now is to open the next prn file within the folder. I want it to continue until it opens all of them in the folder.
Sub Macro1()
Dir ("D:FTP dataOrder Assessment*.prn")
Workbooks.OpenText Filename:="D:FTP dataOrder Assessment*.prn", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(31, 1), Array(44, 1), Array(59, 1), _ Array(72, 1), Array(84, 1), Array(93, 1), Array(103, 1), Array(114, 1), Array(123, 1))
End Sub
View 9 Replies
View Related
Oct 2, 2007
i have a master file which needs to be updated by several other sourcefiles. I did a macro running from the master file which opens all of the sourcefiles one by one, copies the relevant info from the sourcefile to the masterfile, and closes the sourcefile, then moves to the other one. i know this is probably not the case, but the code below seems to behave randomly: sometimes it works, sometimes it bugs by saying file not found runtime error 1004:
Dim masterfile As String
masterfile = "Master.xls"
Dim sourcefile As String
CurrentWeek = InputBox("Enter current week number")
CountWk = 35
sourcefile = "Source" & CountWk
Workbooks.Open (sourcefile)
....
The last statement causes the error. Both master.xls and source35.xls are in the same folder.
View 5 Replies
View Related
Mar 26, 2014
Any way to create individual excel files for over 300 people in one shot.
I have created an excel template and have a list of 300 people. Rather than one at a time, is there a way to do it in one shot?
View 2 Replies
View Related
Sep 28, 2009
I have the following data:
column a: column B:
1
7
9
25
I need a formula to make column B equal to the current month adding the day in column A. so that column B equal the following:
column a: column B:
1 09/1/2009
7 09/7/2009
9 09/9/2009
25 09/25/2009
View 3 Replies
View Related
Mar 9, 2007
the VBA code to allow a user to navigate to any of our network directories and upon selecting (using workbook getopenfilename hopefully) the proper folder on their directory open each txt file (delimited with a pipe) and then save the active file as a xls file and then close it and move on to the next file. I am pretty sure this will take a do while statement but I am not sure how to write it.
View 9 Replies
View Related
Jan 2, 2007
I want to know if its possible to write a macro that will prompt me to open a document, then once open it will copy data and paste it into the working spreadsheet. I have a mini macro that cuts and pastes data elsewhere in the sheet but at present I am manually opening a file then copying all and then pasteing the data which is rather slow.
View 9 Replies
View Related
Mar 27, 2008
I'm having problems with the multiselect argument of the getopenfilename function. I've used this dozens of times with no problems, but now it doesn't work for me. I tried copying and pasting code that works in one module,
What I'm using is
Sub OpenFiles()
Dim vFiles As Variant, iNumfiles As Integer
vFiles = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(vFiles) Then
For iNumfiles = LBound(vFiles) To UBound(vFiles)
MsgBox vFiles(iNumfiles)
Next iNumfiles
End If
End Sub
pretty simple, expect that when I select multiple files in the open dialog box, vfiles is a string containing one of the filenames, not an array containing them all! (by selecting, I simply highlight all the fiels I want and click open)
View 3 Replies
View Related
