File Copy Macro - With Directory Specified In Workbook Cell

Feb 23, 2010

I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination.

The macros do this:

Source and Location directory defined in Sub Archive1.
These are passed to file copy macro Sub ArchiveScript.
This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself.

However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!)

- Invalid procedure call or element on fn = dir() at the bottom

- The copy still works, but only seems to copy one file from the source to the destination, and not all of them.

View 6 Replies


ADVERTISEMENT

Open File Macro: Same Directory As Macro Workbook

Nov 17, 2006

I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.

Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

View 3 Replies View Related

Prompt User For Directory And Filename; Then Save File In Directory With File Name

Jun 10, 2009

I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.

View 3 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

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

Copy File Names From A Directory Into An File

Mar 11, 2007

Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?

View 9 Replies View Related

Copy Selected File To Specified Directory

Jul 23, 2013

Basically what I want to do is to create a button which when pressed opens a file picker (I think its called this way) and the user should find and select a pdf file. And when he does that he clicks "OK" and this file is copied to specified folder.

View 7 Replies View Related

Copy Most Recent File To Another Directory

Sep 11, 2009

I've got this code that will copy data into my worksheet from the most recent .IRP file generated on my C:/ drive. Along with copying the data to my worksheet, I'd like to have a copy of the IRP file copied to a folder on my L:/Drive.

In the code section I've commented out the code that allows the data from the most recent file to be copied to my worksheet. I found code that supposedly will copy a file to a new directory. I want to combine this into my code to allow both to happen. Here's my attempt.

View 5 Replies View Related

Copy Directory If File Exists

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

Copy File Names ,date From A Directory

Feb 20, 2009

I need to copy the names and date modified from all pdf files in a open directory. The directory name will change month to month. I will copy the names into an excel workbook that will be opened as well called Shipped starting in sheet1 cell P2

View 9 Replies View Related

Save Workbook As Copy To Another Directory

May 20, 2009

I need save my workbook as copy to another directory, ( C: ) with VBA code

View 9 Replies View Related

How To Create Copy Of Excel Workbook In Same Directory

Mar 20, 2014

I have Workbook named"Test_Template" in below specified path this is not the active workbook.

It is saved in strFName = ThisWorkbook.Path & "Backup" & "Test_Template.xlsx"

I need to create a copy of the same workbook named as "Copy_Test_Template.xlsx" in the same folder(Backup), same specified path on button click.

[Code] .....

But this creates a "Copy_Test_Template.xlsx outside the Backup folder, i want it in the back up folder.

View 2 Replies View Related

Macro To Erase File On A Directory

Mar 9, 2013

The code bellow allows me to save a copy of the invoice by way of doPDF using invoice number as the name of the pdf to be saved.

Code:

Else
Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _

[Code]...

As there is the need to, should a quotation or an invoice be wrong, to change it. In other words I have code to cancel the invoice as bellow.

Code:

Dim sResponse As String
Dim rFound As Range
sResponse = InputBox("Enter record you want to change." & vbNewLine _& "ATTENTION!

This will change the current record number and will erase record data from saved records sheet.")

If sResponse = "" Then --cancelled or OK with no entry: do nothing or add message
Else
Set rFound = Sheet1.Columns("L").Find(What:=sResponse, LookIn:=xlValues,

[Code]...

The code above simply removes a line on sheet1 to which yet another macro retrieved some info from an invoice such as date, invoice, customer and total.

Now I just realized that when I change an invoice, I will be again when finished, creating another pdf file that already exists.

How can I have the second macro here changed so that it will go to ""E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _" and delete this pdf file?

View 9 Replies View Related

Change File Directory Macro?

Nov 11, 2013

I have a 2 columns.

A: Current location Ex(C:UsersDesktoplabelsA02FAA.jpg)

B: Future location Ex(C:UsersDesktopoxes98300SA02FAA.jpg)

I want to move jpgs from location A to B

is there a way to execute this with a macro?

View 5 Replies View Related

Macro (VBA) To Save File To Web Directory (SharePoint)

Jun 17, 2014

I have recorded the below code that functions fine from my computer. My issue is that I need to export/import this macro to other workstations who may have the save to location mapped to a different drive letter. Is there a way to replace the drive letter with the URL (SharePoint)?

ChDir "E:3. CRQsRemedy Dumps"
ActiveWorkbook.SaveAs Filename:="E:3. CRQsRemedy DumpsToday_CRQ_8Dump.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Save To Directory: \server_1Dir_1Dir_2FolderToday_CRQ_8Dump.xls
Permissions are in place.

View 1 Replies View Related

Excel 2003 :: Macro That Copies And Pastes From Every XLS File In Directory

Oct 23, 2012

What i am trying to do is in 1 workbook (labled as Book1 literally), it needs to copy the sheets out of every .xls file there is in a single directory, we'll call C:MyFolderMySubFolder. There can be anywhere between 1 and 366 files in this particular folder and I need all the sheets in each file labled 'CC' copy that entire sheet, paste that sheet to Book1, go back to that file it was copied from, close it (saving changes is ok), then move on to the next file.. and the next file... and so forth

While pasting into book1, I need each WS copied from each file to paste to a new worksheet in book1 rather than combining them into 1 or overwriting, and lable each of those sheets the file name of which the sheet came from...

The names are in sequence. All files in the folder will be labled as a date such as "9-6-12" so the sheet name in book1 would be named 9-6-12. (so there may result in 366 new worksheets to book1)

I primarily use Microsoft Office Excel 2003

View 5 Replies View Related

Macro That Saves Current Open Workbook To Directory Specified?

Jan 10, 2012

I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.

View 4 Replies View Related

Excel 2013 :: File Size Grows When Using Macro To Copy / Paste To New Workbook

May 9, 2014

I'm using Excel 2013 on Windows 7.

I have an Excel file which has a macro that sucks in data from a number of separate files (two Excel, a varying number of csv). The macro grabs all the individual files and loads them into one of three worksheets with some formatting, sorting, and structuring on the way. All good so far.

I then need to take two of the worksheets and copy them to a new Workbook which I can send out to some users. So I use the Workbooks.Add method, and use the Selection.Copy on just the data (UsedRange.Rows/Columns.Count to ensure it is only the data) and Selection.PasteSpecial with the xlPasteColumnWidths, xlPasteValues, and xlPasteFormats options to copy the data over. None of the data is filtered. Just straight data.

I would expect the newly added workbook to be smaller than the one with the macros because the one with the macros has two extra sheets that I don't transfer, and the sheets I do transfer are identical.

And that's where it gets weird. The file with the macros and more data is 18.7Mb, but the new one with only two sheets is 24.8Mb. One is .xlsm and the other is .xlsx (because it has no macros).

I've tried opening the new workbook and saving as .xlsm (no material difference to the size) and as .xls (it got even bigger). I've opened the new workbook, gone to the end of the data and deleted all the blank rows and columns, but no effect (because the rows are already empty).

Why would the file with less in it be so much bigger (or bigger at all)?

View 2 Replies View Related

Macro: Create Directory Based On Active Workbook And Save To It

Sep 8, 2006

Read “My Documents” Path And Use Result

Problem:

Note:

Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003

1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.

2. Per customer request, VBA application needs to save extracted files for future use.

3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.

4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:

Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................

View 5 Replies View Related

Linking Dynamically To Other Workbook In Directory Specified In Cell?

Mar 6, 2014

Using Dropbox, a lot of people will fill in formulas in personal workbooks from using data from another, central workbook. Since I cannot control where they install Dropbox or where they keep their personal workbooks, I must account for that in functions.

Now, my idea is to make them define the directory of the source in their personal workbook in a specific cell and then using that as a basis for the rest of the functions.

Example

Cell A1: C:Usersuser1Dropboxfolder (copy paste from path in Windows Explorer)

Cell B1: A1&[CentralWorkbook.xlsx]SheetName'!$H$1

Basically I want the unknowing user to copy-paste the path of the central workbook into a specific cell in their own workbook and then build my functions bsed on that. How can this be done?

View 2 Replies View Related

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

Macro To Save Workbook/File As Cell Value

Sep 15, 2006

I need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is.

Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic.

View 3 Replies View Related

Create Directory From Cell & Save Workbook With Cell Value

Dec 3, 2006

what code can be used to be place in a Command Button on a worksheet that will use the value in a cell, change it to the directory where it have to be saved and save it to the value of another cell. Example:

On a worksheet called "Customer", at cel A1 will be the customer's name and at cell A2 will be the asset number. Now let's say a directory does not yet exist on the root for this customer, a new directory must be created in the root directory by using the name in A1, and the workbook must then be copied and saved under the value of A2.

View 6 Replies View Related

Macro Formula For Copy Cell Value From Files To One Workbook

Feb 4, 2014

Actually I do want to copy cell N589 from all files excel (sheet1) in c:database2014 to my workbook click run into the row C.

is there any of you can solve it?

View 1 Replies View Related

Macro - Copy Data Based On Info In Cell From Separate Workbook

Jun 4, 2014

I have an invoicing spreadsheet, what I want is to have a main working page and then tabs after separated by vendor, but save myself a bunch of manual work in the future.

I want to be able to work in the main tab and the other tabs automatically update with the new info.

So I want the macro to search the text in the first column and if it finds "AIM Land Services Ltd." then I want it to populate the appropriate tab, in this case "AIM", with all the respective information after it ie: A-O. But I dont want it to select any other vendors/ info.

This is what I have:

Sub MoveData()
For Each cell In Range("A1:A1") ' Where to look for the date
If cell.Value = "AIM Land Services Ltd." Then ' The date
Range("A5:O350").Select ' What is the range to copy
Selection.Copy ' Copy it
Sheets("Invoice_Log").Select ' Select the Sheet it applies to
Range("A5").Select ' Where to paste the data

[Code] .........

View 1 Replies View Related

Excel 2010 :: Macro To Copy 3 Cell Values And Append To Existing Database File

May 14, 2014

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.

View 1 Replies View Related

Macro To Copy Folders To Path Of Open Workbook Based On Cell Values

Aug 3, 2014

I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.

C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365

I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....

VB:

Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)

[Code]...

This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.

View 2 Replies View Related

Excel 2003 :: Macro Breaks Because Can't Copy Cell And Then Cannot Select Cells In Workbook

Oct 21, 2013

i have a macro that opens 10 other workbooks and copies cells onto a "master" workbook. Until recently, everything worked fine. Suddendly, while it opens the 9 books and copies as it is expected, but when it opens the 10th wb, the macro breaks at a very simple copy/paste.

The weirdest thing is that after it breaks, I cannot select any cells neither in the opened wb nor in the master wb. This continues even after I press the "reset" button in the vba. So, I am only able to select objects (text boxes etc) in my wb and not any cells.

Also, the "arrow" icon on the design toolbar is not active. And I've tried pressing and de-pressing the F8 key, but I still cannot select any cells.

I have option explicit in the beginning of my macro. And i'm using excel 2003.

View 2 Replies View Related

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.

I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.

Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.

Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy

[code]....

View 4 Replies View Related

Copy Workbook Sheets To New File

Jun 6, 2007

I am having difficulties copying a entire workbook into a separate new workbook (to make a number value copy for printing and saving purposes). My problem is the hidden sheets are not always hidden and the unhidden sheets are sometimes hidden. This all depends on a different macro that i am running through the workbook.

example:
sometimes i need sheets 1,2,4 to be copied to the new workbook
other times i need 1,2,3 to be copied to the new workbook

View 5 Replies View Related

SaveAs To Same Directory But Different File Name

Feb 4, 2009

By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").

View 4 Replies View Related







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