VB string to export data to *.bat

[ilian]

Hey Mr.Excel experts,

I'm in need of some assistance here, In order to locate and copy over 1000+ Pdf files I'm trying to create a simple set of strings to apply a wildcard value to a specific column so that each of the entries contains a numerical value (which I already have) and here's the fun part I don't have the alphabetical values which should all be 1 or 2 names. Some of the files may have a Hyphen in between both values.

My goal is to export this list to create a *.bat file so I can run this as needed with little effort.

Anyone have an idea how I can do this?
Here's the excel table I'm using

[TABLE="width: 500"]
<tbody>[TR]
[TD]Output[/TD]
[TD][/TD]
[TD][/TD]
[TD]Current Path folder[/TD]
[TD]File name[/TD]
[TD]Destination folder[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Copy[/TD]
[TD]=CONCATENATE($I$2;$E$2;F2;$I$2)[/TD]
[TD][/TD]
[TD]T:\Users\Main\Test1[/TD]
[TD]test.pdf[/TD]
[TD]T:\Users\Main\Test2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Copy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Copy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Copy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Copy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

as for the VB I tried to use the Amendrightafter function to make sure that the ".pdf" is included but this syntax won't take a wildcard value before the .pdf

Thanks in advance for the replies

 

[Andrew Fergus]

Re: VB sting to export data to *.bat ( I'm so lost!)

Hi

I'm not understanding 100% what you are trying to achieve.

Are you trying to move (or copy?) a number of files from one location to another?
Is it all files in this location?
Is there more than one source location?
Is there more than one destination location?
Is there some sort of criteria for deciding which files to move/copy? (e.g. only pdf files)

The reason I ask is that Excel has a number of in-built functions in VBA for file handling, per this link:
https://msdn.microsoft.com/en-us/library/hww8txat(v=vs.84).aspx/
you can access file properties using VBA and also copy/move files directly from within VBA using the File System Object (FSO).

Once we understand the issue correctly, then I'm sure what you ultimately want to achieve can be done (except for winning the lottery).

Andrew

 

[thisoldman]

Re: VB sting to export data to *.bat ( I'm so lost!)

I prefer the command line for file operations. I'm old and set in my ways – I still call those collections directories, not folders. I'll venture outside the usual forum confines of a VBA approach.

*.bat? My experience would have led me to do this with a linux shell or a perl or python script.

My second thought was, "Bulk copy? Use cmd.com's robocopy command." That command is useful for bulk copying and backup but, I found, it does not allow renaming.

Perhaps a Windows Powershell scriptlet? I would probably have a steep learning curve if I went with this method. Here's some promising leads I found with Google, searching for "powershell bulk copy and rename files".

Asks about using csv with list of old and new filenames.
https://stackoverflow.com/questions...e-powershell-command-to-copy-and-rename-files

General copy, move, and rename with Powershell:
https://www.petri.com/copy-move-rename-files-using-windows-powershell

This might prove helpful (but the website is terrible). A cmd.com approach:
https://www.experts-exchange.com/qu...ename-using-lookup-file-using-Powershell.html

I hope this helps.

 

[ilian]

Re: VB sting to export data to *.bat ( I'm so lost!)

Hey Andrew
Sorry should have been more specific, to answer your questions

Are you trying to move (or copy?) a number of files from one location to another? Copy From a shared drive into a new folder
Is it all files in this location? No , mixed pdf word some excel
Is there more than one source location? 1 root folder but 3-4 sub-folders for sources
Is there more than one destination location? all under 1 root folder
Is there some sort of criteria for deciding which files to move/copy? (e.g. only pdf files) Yes I have a list that contains part of the pdf file name

Thank you ,

 

[Andrew Fergus]

Re: VB sting to export data to *.bat ( I'm so lost!)

Sorry for the late reply. Are you still working on this?

If so, where is the list contained? I presume this is an existing workbook on a specific worksheet?

Lastly, are you able to supply an example of the "list that contains part of the pdf file name"?

Andrew