Excel 2010 :: Copy File / Rename And Move To New Folder
Jan 15, 2013
I am trying to copy a file, rename it, and save it to a new folder. I keep getting a "Compile Error ; Syntax Error" at line FileCopy (ImagePath & oldName, NewPath & newName). I am using Excel 2010.
FileCopy (ImagePath & oldName, NewPath & newName)Sub RenameFiles()
'Renames file based on "sheet 1" - Column 1 Old file name - Column 2 New file name
Dim oldName As String
Dim myfile As String
Dim newName As String
Dim ImagePath As String
Dim NewPath As String
For the last year I have been downloading cash register X1 and Z1 files onto an SD card. Each night's files (X1 & Z1) are stored in a new folder with the naming convention "RP + [DATE]", (EX: RP120910, [YYMMDD]). I have 265 folders! I need to go through each folder, select the Z1 file (I don't care about the X1, X2 or Z2 files) and rename each with the following naming convention: "Z1 + folder_name + .xls". For Example: Folder RP120910 contains the Z1 file named "Z1_T1729.ECR". After renaming, the file should be "Z1RP120910.xls". As the VBA code loops through each subfolder, selecting and renaming the Z1 files, I would like the new files to be placed into a new folder named "Z1Files". Note: All subfolders are currently in a directory named "RegisterFiles". I am using Excel 2010.
I've written most of the code that I need... the problem is that I don't know how to properly select, move and rename the pdf from excel.
I have a popup to select the file, and I know what I want it to be called, what I need is the code to move and rename a non-excel file from inside a macro.
Here is what I have so far:
Sub AttachPDF()
Dim LastName As String Dim Street As String Dim callDate As String Dim FilePath As String Dim HouseNumber As String Dim FullFileName As String Dim FileName As String Dim Scan As String
Range("D32").Select 'Ask user what file they want to attach. Scan = Application. GetOpenFilename(FileFilter:="Follow Up Scan, *.pdf", Title:="Please Choose A PDF To Attach")
I am trying to move and rename an excel template file using a macro. My code works fine when it is moving the file locally on my C: drive. However, when I try to do it on a mapped network drive I get a Path/File access error. Here is the relevant code:
[Code] ......
I tried it both ways that are commented out - both give me the error. I have permissions to read/write in all relevant folders. What am I missing here?
I am trying to copy one worksheet using the "move/copy" function that is available when you right click a tab name and want to copy the worksheet in the same workbook
The steps I'm using are:
1. Right click the tab name 2. Select Move or Copy, 3. Select Create a Copy 4. Click OK
Doing all of the above does not work. When the new worksheet opens, all columns are the same width. It seems to be stuck on "autofit column width" setting of 8.5. The original worksheet is several columns wide all with different width settings.
I've also used Copy, Paste Special and selected column widths and that does not work either.
I have a list of names in sheet1, starting with cell A3. I would like to copy sheet3, insert it after sheet3 and rename the sheet to correspond to the names in the list.
If i have 30 names I would like 30 sheets. If i add a name, I would like to repeat the copy, insert and rename steps for the extra names as i add them.
All I have a macro that goes to a "Main" folder modifies the excel sheet and then saves. Is there a way to move that "Modified" sheet to the correct folder after?
So all of the Excel files are in this folder: C:Documents and Settingsu369875DesktopProject stuffTestin Save_AS
And then at the end of that I need the bldg Number from the front of the excel name. So for the first one : C:Documents and Settingsu369875DesktopProject stuffTestin Save_AS1130
Or I could even put that building # in the excel at lets say (A1) and grab from there if that would work..
Here is the macro I have as of now. It loops through all of the excels in the folder.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?
I have a workbook that filters the data on the worksheet "Reports" this then adds new worksheets and copies and pastes the relevant data that has been sorted, this is done by the name in column "B".
What I need is to rename the new worksheets with the data that is in cell "B2" of the new sheets.
I am using this code for 2nd coping my file and rename it automatically......
[Code] .....
I want when i want another copy always my path should be "C: estabc.xlsm" and For new file in same path "C: estdef.xlsm" an input box ask for the name("def.xlsm") "what new name u want for this new file".
I'd like my macro (in an Excel 2010 workbook) to copy values from three worksheet cells and append them to an existing Access or SQL database file -- without having to leave my Excel spreadsheet or open any other programs. The database file simply needs to be appended. Keeping a running list. That file isn't being used for anything else, we can set it up however is necessary to do this.
For example, Excel cells A1, A2, and A3. Representing Job Name, Job Number and Job Total.
how make VBA do this(in SIMPLE terms)? Or give me a link of where to look or what to search for? I understand VBA but not Access or SQL.
I have some code which will look for a specific file in a given location. Is it possible to use VBA to rename that file without opening/re-saving and killing the specific file?
I have windows 7 and excel 2010 and am using a macro that opens up a csv file(I think) of daily reports into Excel and then automatically delineates it and formats it how I want it. I will be using this to save a new file every day for the reports from the previous day and want to include at the end of that macro a way to prompt the user to "save as" so that each day they can run the macro and enter in the date and save that report for further use. I am wondering what VBA code I could use at the end of the macro code to prompt the Save As box and if I could already have the save us set up in the following folder... "W:Daily to Fortis Excel2014(the user will put in the date here)".
I've been looking around sites and trying to figure it out. I need the file format to be the same as when you save as "Excel Workbook". I was trying to use the Saveas (filename) function and could get it to save every time as a specific file name in that location but when I run it the next day it has the same name and saves over itself. So I need the user to be able to put in todays date as the filename to create a new one every day.
We updated excel from 2003 -> 2010 and this stopped working.
We have a macro which takes pictures from a folder and pastes those pictures in the excel -sheet.
At 2003 the pictures were correctly pasted to their positions, but at 2010 the pictures appear in somewhere at the same sheet. (not at the right places, which should be at column "o" at the same row.)
Macro (working correctly at 2003) :
Sub Kuvan_piirto(ic As Integer) On Error GoTo loppu
getting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.
I have some daily text files in a folder (so about 30 of them each month), which in the end of month, I need to open them up in excel, format them so that I can use the information for my analysis.
I would like to create a macro, to quickly open them all up at once and save them each individually in .xls or .xlsm format.
I am new to VBA and after some research online, I was able to have the files open with the following code. but now I don't know how to proceed further to save them one by one with the same name but in .xls or .xlsm format.
Sub Opentxtfiles() Dim MyFolder As String Dim myfile As String
I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.
Excel 2010, Windows 8.1. I want to retrieve a photo from a folder and paste it into a cell. I have the paste and format process figured out but I'm having trouble putting the file path together to retrieve the photo. If I use the full path, the statement below retrieves "IMAG1234.jpg" and puts it where I want it.
I have a bill of materials for each job I do, I have multiple cut sheets in one spot on the network that must be individually copied and pasted into the project file for each job. I’m hoping to automate the process. Post extraction dump from AutoCAD, my text file goes into a excel file that the information is used by multiple tabs including a bill of materials.
From the bill of materials, I want to say “if C3>0 get file 10P0044HP2.DOC” . . . I want it to get the file and save to the individual project file. .. Possible I’m not so sure. But I’m not sure of where to start.
My Excel 2010 spreadsheet contains client data like the below:
A B C D E
What I'm trying to get from this is a personally addressed email with 2 attachments, one will be standard to all recipients and one will be unique and specific to that recipient. The filename of the unique attachment will contain the reference but will have some other stuff in the filename as well on either side that I will not be able to remove.
In terms of the file locations the unique ones will be in subfolders of the folder holding this workbook and the generic one will be in the same folder as the this workbook.This is some adapted code that solved a similar problem (in Excel 2007 though) on a different website.
VB: Sub Mail_Report() Dim OutApp As Object Dim OutMail As Object [code]....
In that case the file started with the "reference" field but in my case it is in the middle and the formats vary depending on the provider, there will only be a maximum of 5 providers but I would like to avoid 5 different macros if at all possible so I need a search function of some sort.
I have obtained a piece of code from online that runs a macro on multiple files in a single given a single folder. when i implement it though it throws a run time error 424 "object required' and point to 'Documents.Open FileName:=path & file' line of code. Is this because Dim file type is not declared?how do i fix this problem. also, im using excel 2010.A
I'm working on a project where I need to import 150 csv files into an Excel2010 workbook, with each csv file being on a seperate worksheet.
I would like to set up a macro to point to the folder I will have these saved in this up so that each month when I get updated csv files I can repeat the process and bring in the new data. how to create this, I'm very new to VBA?
