-
Code for remanimg files from a field in a table
We are changing Document Control system software. Our current system saves the documents with a unique file number; it also strips the file properties. There is a table on the SQL server that lists the unique number, and file type (e.g. Word Doc, Excel). What I need to do is have some code grab the file, rename it based on the doc number and docuemnt title found in the table and reassign the file properties.
How difficult of code of this type, and what, in general, would it look like?
As I get more details I will add them so I can get the help I really need!
Well, at least the help with the code...
-
You could use assemble the Old and New file names and loop through using the Name command. (Make sure you have a backup first!!!)
Name Statement Example
This example uses the Name statement to rename a file.
For purposes of this example, assume that the directories or folders that are specified already exist.
On the Macintosh, “HD:” is the default drive name and portions of the pathname are separated by
colons instead of backslashes.
[VBA]
Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.
OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.
[/VBA]
You could also consider copying to a new location, before you delete the old files
[vba]Dim SourceFile, DestinationFile
SourceFile = "SRCFILE" ' Define source file name.
DestinationFile = "DESTFILE" ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
That's a start...but now how do I link that to the table that contains the old file name (the unique number the system assigns it) and the new file name (from the appropriate field in that same table)
-
If you can export the data to an Excel file and post it as a sample, that would assist.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules