A little knowledge is a dangerous thing
March 5, 2008 11:20 AM Subscribe
Help me automate this task involving USB-disks, Excel, HTML and maybe a batch file.
Once a week I hand out about 20 USB flashdisks. I add the names of the people who received them to a file as I hand them out, and also add a textfile to the USB renamed to their own name. I am now also going to be adding another couple of files, a HTML file and an autorun, but that's a simple matter of a batch file. I am not locked to using Excel, I can use a tab-sep text file instead if it's any simpler.
What I was thinking was to log the persons name in Excel, and then click a button in the next column that executes a command containing the name from the cell. Problem here being I don't know VBA.
I realise this is a typical "That's just a few lines of perl!" question, but unfortunatly I haven't exactly come that far yet. I feel I am missing some obvious solution.
Once a week I hand out about 20 USB flashdisks. I add the names of the people who received them to a file as I hand them out, and also add a textfile to the USB renamed to their own name. I am now also going to be adding another couple of files, a HTML file and an autorun, but that's a simple matter of a batch file. I am not locked to using Excel, I can use a tab-sep text file instead if it's any simpler.
What I was thinking was to log the persons name in Excel, and then click a button in the next column that executes a command containing the name from the cell. Problem here being I don't know VBA.
I realise this is a typical "That's just a few lines of perl!" question, but unfortunatly I haven't exactly come that far yet. I feel I am missing some obvious solution.
Response by poster: Wow! That works astoundingly well, and I actually understand it to boot! A little googling even showed me how to add in the filecopy commands so I don't need to run the batch. Learn something new every day!
It does prompt two big questions though, hopefully you can followup for me?
1) If I were to do something similar with a function I would have it in say C2 and then "drag" it down to copy it into the cells below, with the references becoming B3, B4 etc automatically. The macro as is above is run from the macro interface or a button, it is just for the one field... is there a way to "drag" the macro down so I can activate it for each row in turn? So, add all the names and then in with the usb, "CLICK", switch to the next usb, and then CLICK on the next name?
2) Can I specify the file be made read only at the end?
Hope point 1 is clear enough that you understand what I mean. VBA looks really powerful for what it is, I think I may have to explore more later.
posted by Iteki at 1:03 PM on March 5, 2008
It does prompt two big questions though, hopefully you can followup for me?
1) If I were to do something similar with a function I would have it in say C2 and then "drag" it down to copy it into the cells below, with the references becoming B3, B4 etc automatically. The macro as is above is run from the macro interface or a button, it is just for the one field... is there a way to "drag" the macro down so I can activate it for each row in turn? So, add all the names and then in with the usb, "CLICK", switch to the next usb, and then CLICK on the next name?
2) Can I specify the file be made read only at the end?
Hope point 1 is clear enough that you understand what I mean. VBA looks really powerful for what it is, I think I may have to explore more later.
posted by Iteki at 1:03 PM on March 5, 2008
Response by poster: Perhaps "activecell" is something I could use, running it on each cell I am sitting on, will experiment in the meantime.
posted by Iteki at 1:35 PM on March 5, 2008
posted by Iteki at 1:35 PM on March 5, 2008
Response by poster: Scuse the triple post, but google and some common sense has answered my two questions. I used ActiveCell so that I can just run it on each cell as I go along, and I found how to set read only. Thanks once again pompomtom, this was really good help!
posted by Iteki at 1:48 PM on March 5, 2008
posted by Iteki at 1:48 PM on March 5, 2008
Heh... just got into work and thought "I really should test that". Glad it worked.
posted by pompomtom at 2:15 PM on March 5, 2008
posted by pompomtom at 2:15 PM on March 5, 2008
This thread is closed to new comments.
The code for your button (presuming the name is cell B2, and the drive is X:) would be something like:
--------------------------
sub commandbutton1_click
open "X:\" & range("B2").value & ".txt" for output as 1
print #1, "This will go into the text file, so put whatever you'd like here."
print #1, "Or here."
close 1
end sub
-------------------------
posted by pompomtom at 12:39 PM on March 5, 2008