 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
Jow
Joined: 11 Jan 2008
Posts: 34
|
 Posted: Fri Jul 11, 2008 10:15 am Post subject: Renaming files in folder |
 |
|
I've got a macro that I've written in Excel which works perfectly, but I need a way of running this via an .exe in windows, rather than having to run it in excel everytime because i need to give it to a customer to use.
I thought AHK would be a good solution, here is my VBA code....(Obviously if you don't know VBA, just skip this and read the description of what I need underneath  )
| Code: |
Dim mpfile As String
Dim mpwb As Workbook
mpfile = Dir("\\Server-1\production_share\Policy Services Live Work - Officenet\Image Renaming\*.csv", vbNormal)
Do While mpfile <> ""
Set mpwb = Workbooks.Open("\\Server-1\production_share\Policy Services Live Work - Officenet\Image Renaming\" & mpfile)
Name Replace(mpwb.FullName, ".csv", ".pdf") As _
"\\Server-1\production_share\Policy Services Live Work - Officenet\Image Renaming\Completed\" & mpwb.Worksheets(1).Range("A1").Value & ".pdf"
mpwb.Close savechanges:=False
mpfile = Dir
Loop
|
Here is a desctiption of what I need doing...
What I have is a folder with a number of excel files and a number of pdf files with the same name, for example..
file1.xls
file1.pdf
file2.xls
file2.pdf
file3.xls
file3.pdf
Now, within each excel file (always in cell A1) there is a number.
What I want to do is write a script that will open up each excel file within the folder in turn, and rename the pdf of the same name with the number in cell A1. And also move the renamed PDF into a new folder (would be a fixed directory).
So the macro will open up file1.xls, and assuming the value of cell A1 was '7345', it will then rename file1.pdf as '7345' and move it into a folder called "renamed pdf files".
And continue to do the same thing for file2.xls and file3.xls etc
I'm fairly clueless as to how to convert my VBA above into an AHK script that does this. Can anyone help? |
|
| Back to top |
|
 |
Krogdor
Joined: 18 Apr 2008
Posts: 903
Location: The Interwebs
|
| Posted: Fri Jul 11, 2008 11:02 am Post subject: |
|
|
| Code: |
DestinationForPDF=C:\NewPDF
FolderWithFiles:=C:\OldFiles ;no trailing backslash on either
Loop, %FolderWithFiles%\*.csv ;for all files in the folder:
{
FileRead, CSVFile, %A_LoopFileLongPath% ;load file to variable
Loop, Parse, %CSVFile%, csv ;parse the csv file
{
CellContents:=A_LoopField ;get the first field...
Break ; ...then stop
}
FileMove, %A_LoopFileDir%\%A_LoopFileName%.pdf, %DestinationForPDF%\%CellContents%.pdf ;move/rename file
}
|
Not tested, should work I think.
Edit:
Oh, wait. Your code had .csv files, but you said you needed .xls files... Well, that complicates things, since I'm almost positive AHK won't be able to read .xls files. I'm... not quite sure if there would be any way to do it without opening Excel... although if that's okay then it shouldn't be too complicated, I guess. |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Fri Jul 11, 2008 11:19 am Post subject: |
|
|
Krogdor, thanks for your reply. I am actually working with CSV files, I mentioned the use of XLS files purely because i didn't want to create any confusion because I've had some issues where people aren't familiar with CSV files.
Thanks for your suggestion, I'll have a go with it |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Fri Jul 11, 2008 11:47 am Post subject: |
|
|
| Code: | DestinationForPDF=\\Server-1\production_share\Invoicing\test
FolderWithFiles:=\\Server-1\production_share\Invoicing\test\renamed pdf files ;no trailing backslash on either
Loop, %FolderWithFiles%\*.csv ;for all files in the folder:
{
FileRead, CSVFile, %A_LoopFileLongPath% ;load file to variable
Loop, Parse, %CSVFile%, csv ;parse the csv file
{
CellContents:=A_LoopField ;get the first field...
Break ; ...then stop
}
FileMove, %A_LoopFileDir%\%A_LoopFileName%.pdf, %DestinationForPDF%\%CellContents%.pdf ;move/rename file
}
|
I've put in my own directories, does it look right?
I get this error message when I run it -
"Error at line 2.
Line Text: \\Server
Error: The leftmost character above is illegal expression.
The program will exit'
Can you see what the issue is?
Also, I don't want to sound too demanding! But is it possible to make this so that it asks for the user to browse to the target directory first, and then overwrite the PDF file names within the same folder instead of moving them?
It would be better if the renamed files were moved to a subfolder, but because the customer will be using this on whatever folder they select, it's unlikely that a subfolder with the correct name would exist. Unless it would be possible to create the subfolder if it didn't already exist and then move the files into it? If this is tricky to do though, it is acceptable just for the PDF files to be renamed within the current folder and not moved.
Back to the question of selecting a directory...I have tried to add some code myself, but it wasn't successful :p
Here is the bit of code I was playing with...
| Quote: | ;//dir=%A_MyDocuments%\Excel
FileSelectFolder, dir,\\Server-1\production_share
if dir =
ExitApp
else
|
I really appreciate any help I get |
|
| Back to top |
|
|
engunneer
Joined: 30 Aug 2005
Posts: 6772
Location: Pacific Northwest, US
|
| Posted: Fri Jul 11, 2008 5:00 pm Post subject: |
|
|
change it from := to = on line 2.
| Code: |
FolderWithFiles=\\Server-1\production_share\Invoicing\test\renamed pdf files ;no trailing backslash on either
|
your other questions are all possible and easy.
FileCreateDir can make the subfolder of your choice.
When using FileSelectFolder, it is best to do something like:
| Code: |
TryAgain:
FileSelectFolder, dir,\\Server-1\production_share
If (ErrorLevel = 1)
{
Msgbox, 4,PROGRAM TITLE, Folder Not Selected.`nDo you want to quit?
IfMsgBox, Yes
ExitApp
else
Goto TryAgain
}
|
Then you can use If FileExist() to see if the folder is there and FileCreateDir to create it if not.
_________________
 Unless otherwise noted, all code is untested.
Common Answers: 1.(Loops, Viruses, etc.) 2. Search 3.RTFM |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Tue Jul 15, 2008 11:27 am Post subject: |
|
|
This my attempt based on your suggestions then, does it look right?
I'm not getting the error at line 2 anymore, but I am getting this error -
Error: The following variable name contains an illegal character:
"3788145
"
The current thread will exit.
---> Line#
021:Loop,Parse,%CSVFile%,csv
Here's the code...
| Code: | TryAgain:
FileSelectFolder, dir,\\Server-1\production_share
If (ErrorLevel = 1)
{
Msgbox, 4,PROGRAM TITLE, Folder Not Selected.`nDo you want to quit?
IfMsgBox, Yes
ExitApp
else
Goto TryAgain
}
DestinationForPDF=%dir%
FolderWithFiles=%dir% ;no trailing backslash on either
Loop, %FolderWithFiles%\*.csv ;for all files in the folder:
{
FileRead, CSVFile, %A_LoopFileLongPath% ;load file to variable
Loop, Parse, %CSVFile%, csv ;parse the csv file
{
CellContents:=A_LoopField ;get the first field...
Break ; ...then stop
}
FileMove, %A_LoopFileDir%\%A_LoopFileName%.pdf, %DestinationForPDF%\%CellContents%.pdf ;move/rename file
} |
Could you give me a hand modifying this to check if a 'completed' sub-folder exists, and if not, create it and move files into it? |
|
| Back to top |
|
|
engunneer
Joined: 30 Aug 2005
Posts: 6772
Location: Pacific Northwest, US
|
| Posted: Tue Jul 15, 2008 2:39 pm Post subject: |
|
|
Please read the documentation when it tells you you have an error. in this case, Loop, Parse wants:
| Help File wrote: |
InputVar: The name of the variable whose contents will be analyzed. Do not enclose the name in percent signs unless you want the contents of the variable to be used as the name.
|
It can save you three hours of waiting around for a response in some cases.
_________________
Unless otherwise noted, all code is untested.
Common Answers: 1.(Loops, Viruses, etc.) 2. Search 3.RTFM |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Wed Jul 16, 2008 9:51 am Post subject: |
|
|
Thanks enguneer, I don't get the error message now. And yes, in future I'll check documentation first!
Although it runs error free now, it doesn't work for some reason, once I have selected the directory and click OK, nothing happens. I'll post the code again just for the sake of clarity...Can anyone see what's up?
| Code: | TryAgain:
FileSelectFolder, dir,\\Server-1\production_share
If (ErrorLevel = 1)
{
Msgbox, 4,PROGRAM TITLE, Folder Not Selected.`nDo you want to quit?
IfMsgBox, Yes
ExitApp
else
Goto TryAgain
}
DestinationForPDF=dir
FolderWithFiles=dir ;no trailing backslash on either
Loop, %FolderWithFiles%\*.csv ;for all files in the folder:
{
FileRead, CSVFile, %A_LoopFileLongPath% ;load file to variable
Loop, Parse, %CSVFile%, csv ;parse the csv file
{
CellContents:=A_LoopField ;get the first field...
Break ; ...then stop
}
FileMove, %A_LoopFileDir%\%A_LoopFileName%.pdf, %DestinationForPDF%\%CellContents%.pdf ;move/rename file
} |
Also, I've looked into filexist and FileCreateDir, would the following code be correct? I want it to check if a subfolder called 'completed' exists within the selected directory, and to create it if it doesn't...
| Code: | IfNotExist, dir\Completed
FileCreateDir, dir\Completed |
|
|
| Back to top |
|
|
tonne
Joined: 06 Jun 2006
Posts: 1159
Location: Denmark
|
| Posted: Wed Jul 16, 2008 10:14 am Post subject: |
|
|
Check this out:
| Quote: | Storing values in variables: To store a string or number in a variable, there are two methods: traditional and expression. The traditional method uses the equal sign operator (=) to assign unquoted literal strings or variables enclosed in percent signs. For example:
MyNumber = 123
MyString = This is a literal string.
CopyOfVar = %Var% ; With the = operator, percent signs are required to retrieve a variable's contents.
By contrast, the expression method uses the colon-equal operator (:=) to store numbers, quoted strings, and other types of expressions. The following examples are functionally identical to the previous ones:
MyNumber := 123
MyString := "This is a literal string."
CopyOfVar := Var ; Unlike its counterpart in the previous section, percent signs are not used with the := operator.
The latter method is preferred by many due to its greater clarity, and because it supports an expression syntax nearly identical to that in many other languages. |
_________________
there's a dog barking close within the range of my ear
sounds like he wants to escape the chain
he would probably bite me to death if he could
but the chain lets me spit in his face
- Kashmir |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Wed Jul 16, 2008 11:23 am Post subject: |
|
|
Thanks tonne, I think I have corrected those issues? As far as I understand anyway.
I've tried something a bit different but, again, nothing happens once I have run the program and selected the directory...Seeing as I tried writing my own code here, the errors may be obvious...
| Quote: |
TryAgain:
FileSelectFolder, dir,\\Server-1\production_share
If (ErrorLevel = 1)
{
Msgbox, 4,PROGRAM TITLE, Folder Not Selected.`nDo you want to quit?
IfMsgBox, Yes
ExitApp
else
Goto TryAgain
}
files=*.csv
Loop, %dir%\%files%
{
Loop, Read, %A_LoopFileFullPath%
{
CellContents:=A_LoopField
Break ; ...then stop
}
FileMove, %A_LoopFileName%.pdf, %dir%\%Completed%\%CellContents%.pdf
}
|
|
|
| Back to top |
|
|
tonne
Joined: 06 Jun 2006
Posts: 1159
Location: Denmark
|
| Posted: Wed Jul 16, 2008 11:28 am Post subject: |
|
|
Maybe use some msgbox to show debug information or use the listvars command (and pause).
You can read the first line with the FileReadLine command - avoids one of the loops.
_________________
there's a dog barking close within the range of my ear
sounds like he wants to escape the chain
he would probably bite me to death if he could
but the chain lets me spit in his face
- Kashmir |
|
| Back to top |
|
|
Krogdor
Joined: 18 Apr 2008
Posts: 903
Location: The Interwebs
|
| Posted: Wed Jul 16, 2008 11:32 am Post subject: |
|
|
| engunneer wrote: | change it from := to = on line 2.
|
Oops, typo x_x
| Jow wrote: |
Error: The following variable name contains an illegal character:
"3788145
"
The current thread will exit.
---> Line#
021:Loop,Parse,%CSVFile%,csv
|
...more oops. I always forget to give parsing loops a variable instead of a string...
Anyway. Now to help with the new problem  Hopefully no mistakes this time.
| Code: | TryAgain:
FileSelectFolder, dir,\\Server-1\production_share
If (ErrorLevel = 1)
{
Msgbox, 4,PROGRAM TITLE, Folder Not Selected.`nDo you want to quit?
IfMsgBox, Yes
ExitApp
else
Goto TryAgain
}
files=*.csv
Loop, %dir%\%files%
{
FileReadLine, CSVLine, %A_LoopFileFullPath%, 1 ;shorter than a file-read loop if you only need one line
Loop, Parse, CSVLine, csv
{
CellContents:=A_LoopField
Break ; ...then stop
}
StringRight, FileName, A_LoopFileName, 4 ;A_LoopFileName includes extension, so remove it
FileMove, %A_LoopFileDir\%FileName%.pdf, %dir%\%Completed% \%CellContents%.pdf
} |
Also.. the variable Completed has been defined somewhere in the script, right? |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Wed Jul 16, 2008 11:55 am Post subject: |
|
|
Hey Krogdor, thanks again for your help
The 'Completed' bit refers to a sub-folder that I want the files to be moved into... |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Wed Jul 16, 2008 12:20 pm Post subject: |
|
|
| Code: | TryAgain:
FileSelectFolder, dir,\\Server-1\production_share
If (ErrorLevel = 1)
{
Msgbox, 4,PROGRAM TITLE, Folder Not Selected.`nDo you want to quit?
IfMsgBox, Yes
ExitApp
else
Goto TryAgain
}
files=*.csv
Loop, %dir%\%files%
{
FileReadLine, CSVLine, %A_LoopFileFullPath%, 1 ;shorter than a file-read loop if you only need one line
Loop, Parse, CSVLine, csv
{
CellContents:=A_LoopField
Break ; ...then stop
}
StringRight, FileName, A_LoopFileName, 4 ;A_LoopFileName includes extension, so remove it
FileMove, %A_LoopFileDir\%FileName%.pdf, %dir\%CellContents%.pdf } |
If I remove the 'Completed' part (as in code above), should it rename the files within the current folder?
If so, I have tried this, and it still does nothing when the program is run  |
|
| Back to top |
|
|
Jow
Joined: 11 Jan 2008
Posts: 34
|
| Posted: Thu Jul 17, 2008 9:59 am Post subject: |
|
|
This thread has probably got a bit confusing for anyone reading it, so I'll just a post a summary of where I am...what I want it to do is still the same as the original post, except now I am allowing the user to select a target directory, and also, I want to check to see if a subfolder named 'completed' exists, and if not, create it and move the renamed files into it. The ifnotexist check hasn't been included in the code yet because I wanted to get the rest of the code to work first...
Here's the code I have so far...when I run it, and have selected the directory, nothing happens. I have been reading lots of documentation to try and figure it out for myself, but I can't see where the problem is.
| Code: | TryAgain:
FileSelectFolder, dir,\\Server-1\production_share
If (ErrorLevel = 1)
{
Msgbox, 4,PROGRAM TITLE, Folder Not Selected.`nDo you want to quit?
IfMsgBox, Yes
ExitApp
else
Goto TryAgain
}
files=*.csv
Loop, %dir%\%files%
{
FileReadLine, CSVLine, %A_LoopFileFullPath%, 1
Loop, Parse, CSVLine, csv
{
CellContents:=A_LoopField
Break ; ...then stop
}
StringRight, FileName, A_LoopFileName, 4
FileMove, %FileName%.pdf, %CellContents%.pdf
} |
|
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
