Dear experts:

I got 80 single ods-files (created by Excel but then saved as ods-
files). They are all stored in the following directory: C:\Graphics\

The file naming convention of these 80 single ods-files is as
follows:

Martin_Graphics_10.ods
Martin_Graphics_12.ods
Martin_Graphics_16.ods
.....
Martin_Graphics_100.ods

The folder C:\Graphics contains 80 subfolders where all these files
are to be copied into. Their naming is as follows:

Chapter_10_ready (C:\Graphics\Chapter_10_ready)
Chapter_12_ready (C:\Graphics\Chapter_12_ready)
Chapter_16_ready (C:\Graphics\Chapter_16_ready)
....
Chapter_100_ready (C:\Graphics\Chapter_100_ready)

Now here comes my question: Is it possible to copy these files into
their respective folder, using VBA?
The numbers are not incremented by 1 but irregular, but they are
sorted. So are the folders where the files should be stored.

Any professional help would be much appreciated.

Thank you very much in advance. Regards, Andreas

To get you going have a look at VBA:
- Microsoft Scripting Runtime library - you'd be interested in
FileSystemObject
- NAME method - it elegantly moves files over.

post back if you get stuck.


On Oct 1, 8:58*am, andreashermle wrote:
Dear experts:

I got 80 single ods-files (created by Excel but then saved as ods-
files). They are all stored in the following directory: C:\Graphics\

The file naming convention of these 80 single ods-files is as
follows:

Martin_Graphics_10.ods
Martin_Graphics_12.ods
Martin_Graphics_16.ods
....
Martin_Graphics_100.ods

The folder C:\Graphics contains 80 subfolders where all *these files
are to be copied into. Their naming is as follows:

Chapter_10_ready (C:\Graphics\Chapter_10_ready)
Chapter_12_ready (C:\Graphics\Chapter_12_ready)
Chapter_16_ready (C:\Graphics\Chapter_16_ready)
...
Chapter_100_ready (C:\Graphics\Chapter_100_ready)

Now here comes my question: Is it possible to copy these files into
their respective folder, using VBA?
The numbers are not incremented by 1 but irregular, but they are
sorted. So are the folders where the files should be stored.

Any professional help would be much appreciated.

Thank you very much in advance. Regards, Andreas

On Oct 1, 11:54*am, AB wrote:
To get you going have a look at VBA:
- Microsoft Scripting Runtime library - you'd be interested in
FileSystemObject
- NAME method - it elegantly moves files over.

post back if you get stuck.

On Oct 1, 8:58*am, andreashermle wrote:



Dear experts:

I got 80 single ods-files (created by Excel but then saved as ods-
files). They are all stored in the following directory: C:\Graphics\

The file naming convention of these 80 single ods-files is as
follows:

Martin_Graphics_10.ods
Martin_Graphics_12.ods
Martin_Graphics_16.ods
....
Martin_Graphics_100.ods

The folder C:\Graphics contains 80 subfolders where all *these files
are to be copied into. Their naming is as follows:

Chapter_10_ready (C:\Graphics\Chapter_10_ready)
Chapter_12_ready (C:\Graphics\Chapter_12_ready)
Chapter_16_ready (C:\Graphics\Chapter_16_ready)
...
Chapter_100_ready (C:\Graphics\Chapter_100_ready)

Now here comes my question: Is it possible to copy these files into
their respective folder, using VBA?
The numbers are not incremented by 1 but irregular, but they are
sorted. So are the folders where the files should be stored.

Any professional help would be much appreciated.

Thank you very much in advance. Regards, Andreas- Hide quoted text -

- Show quoted text -

Dear AB,

thank you very much for your tip. To be honest with you, I got no idea
how to put your tip into practice.

Regards, Andreas

Ok then try playing with this code - it does what you need:
- it doesn't have any error trapping (i.e., your file/folder names
must be consistent)
- doesn't work for files/folders with '100' in it - you just need to
tweak the code to allow for that as currently it would treat the '100'
as '00', i.e., it would drop the '1'.
The code:

Sub MoveODS_Around()
Dim fso As Object
Dim mF As Object
Dim ndx As Long
Dim posExt As Long

Set fso = CreateObject("Scripting.FileSystemObject")

'Next loops through all the files that exist in C:\Graphics
For Each mF In fso.GetFolder("C:\Graphics").Files
'Find the position of the extension in the file name
posExt = InStr(1, mF.Name, ".")
'Find what the Index number in your file is, e.g., if the file
name: _
Martin_Graphics_12.ods _
then its index is '12'.
ndx = Mid(mF.Name, posExt - 2, 2) 'For 100 would return 00
'Moves the file to the folder with the same Index in its name
mF.Move "C:\Graphics\Chapter_" & ndx & "_ready\"
Next

End Sub

Post back if you get stuck.



Dear AB,

thank you very much for your tip. To be honest with you, I got no idea
how to put your tip into practice.

Regards, Andreas- Hide quoted text -

- Show quoted text -

On 4 Okt., 11:33, AB wrote:
Ok then try playing with this code - it does what you need:
- it doesn't have any error trapping (i.e., your file/folder names
must be consistent)
- doesn't work for files/folders with '100' in it - you just need to
tweak the code to allow for that as currently it would treat the '100'
as '00', i.e., it would drop the '1'.
The code:

Sub MoveODS_Around()
* * Dim fso As Object
* * Dim mF As Object
* * Dim ndx As Long
* * Dim posExt As Long

* * Set fso = CreateObject("Scripting.FileSystemObject")

* * 'Next loops through all the files that exist in C:\Graphics
* * For Each mF In fso.GetFolder("C:\Graphics").Files
* * * * 'Find the position of the extension in the file name
* * * * posExt = InStr(1, mF.Name, ".")
* * * * 'Find what the Index number in your file is, e.g., if the file
name: _
* * * * * * * * Martin_Graphics_12.ods _
* * * * * * * * then its index is '12'.
* * * * ndx = Mid(mF.Name, posExt - 2, 2) 'For 100 would return 00
* * * * 'Moves the file to the folder with the same Index in its name
* * * * mF.Move "C:\Graphics\Chapter_" & ndx & "_ready\"
* * Next

End Sub

Post back if you get stuck.



Dear AB,

thank you very much for your tip. To be honest with you, I got no idea
how to put your tip into practice.

Regards, Andreas- Hide quoted text -

- Show quoted text -- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Dear AB:

ok, thank you very much. I will give a try and let you know.

Regards, Andreas

On 4 Okt., 15:02, andreashermle wrote:
On 4 Okt., 11:33, AB wrote:





Ok then try playing with this code - it does what you need:
- it doesn't have any error trapping (i.e., your file/folder names
must be consistent)
- doesn't work for files/folders with '100' in it - you just need to
tweak the code to allow for that as currently it would treat the '100'
as '00', i.e., it would drop the '1'.
The code:

Sub MoveODS_Around()
* * Dim fso As Object
* * Dim mF As Object
* * Dim ndx As Long
* * Dim posExt As Long

* * Set fso = CreateObject("Scripting.FileSystemObject")

* * 'Next loops through all the files that exist in C:\Graphics
* * For Each mF In fso.GetFolder("C:\Graphics").Files
* * * * 'Find the position of the extension in the file name
* * * * posExt = InStr(1, mF.Name, ".")
* * * * 'Find what the Index number in your file is, e.g., if the file
name: _
* * * * * * * * Martin_Graphics_12.ods _
* * * * * * * * then its index is '12'.
* * * * ndx = Mid(mF.Name, posExt - 2, 2) 'For 100 would return 00
* * * * 'Moves the file to the folder with the same Index in its name
* * * * mF.Move "C:\Graphics\Chapter_" & ndx & "_ready\"
* * Next

End Sub

Post back if you get stuck.

Dear AB,

thank you very much for your tip. To be honest with you, I got no idea
how to put your tip into practice.

Regards, Andreas- Hide quoted text -

- Show quoted text -- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Dear AB:

ok, thank you very much. I will give a try and let you know.

Regards, Andreas- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi AB,

works like a charm.

Thank you very much for your professional help. I really appreciate
it.

Regards, Andreas

No worries!
Thanks for the feedback.