Macro to save to USB drive
waea
-
waea Posted messages 43 Status Membre -
waea Posted messages 43 Status Membre -
Hello,
here is my little problem: I can't write a macro that allows me to save an Excel file to a USB stick whose letter can change (F, G, etc.); all of this connected to a small button.
the file to be saved is an xlsm file, so it works with macros.
can anyone help me solve this riddle?
thank you in advance
here is my little problem: I can't write a macro that allows me to save an Excel file to a USB stick whose letter can change (F, G, etc.); all of this connected to a small button.
the file to be saved is an xlsm file, so it works with macros.
can anyone help me solve this riddle?
thank you in advance
Configuration: Windows Vista Firefox 3.5.7
13 réponses
Good evening
function to know the letter of the connected USB
End Sub
--
:-x
function to know the letter of the connected USB
Function usb_letter() computer = "." Set WMI_object = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" & computer & "\root\cimv2") Set driver_list = WMI_object.ExecQuery _ ("Select * from Win32_LogicalDisk") For Each driver In driver_list If driver.DriveType = 2 Then usb_letter = driver.DeviceID Exit Function End If Next usb_letter = "no USB drive connected" End Function Sub test() msgbox usb_letter End Sub
--
:-x
Good evening and thank you for your response...............but
to gain precision, here is what I would like;
a dialog box that immediately connects me to the USB key and to the file in question
with the option to change the file name if by chance I wanted to have several for different reasons
then, obviously, since the dialog box is standard, I would just have to save my work
I hope all of this is clearer
thank you in advance
to gain precision, here is what I would like;
a dialog box that immediately connects me to the USB key and to the file in question
with the option to change the file name if by chance I wanted to have several for different reasons
then, obviously, since the dialog box is standard, I would just have to save my work
I hope all of this is clearer
thank you in advance
Well, it’s basic VBA!
You have the letter, you know the path on your key, so you use open filename
or you change the driver by
chdir the usb letter
then get openfilename you can do this with the macro recorder...
--
:-x
You have the letter, you know the path on your key, so you use open filename
or you change the driver by
chdir the usb letter
then get openfilename you can do this with the macro recorder...
--
:-x
Hello,
thank you first of all, but I would appreciate having an idea of the code written in case the letter of the key might change!
all of this with the information I provided above
thank you again
thank you first of all, but I would appreciate having an idea of the code written in case the letter of the key might change!
all of this with the information I provided above
thank you again
Good evening,
do you want to save to the same USB drive again? If so, we could use the name of the drive, like here....
otherwise, we could also save to the first detected USB drive (in case there are multiple ones, but if there's an external drive, it might get prioritized too) that is "writable" (in case of a protected drive).... To avoid the external drive issue, we could also consider saving in a specific folder (so if it's missing, it's not the right drive...)... anyway, there are plenty of options (most likely passing through WMI as michel_m suggests)... let us know....
--
A beautiful solution is understood as the simple and easy solution to a difficult and complicated problem.
do you want to save to the same USB drive again? If so, we could use the name of the drive, like here....
otherwise, we could also save to the first detected USB drive (in case there are multiple ones, but if there's an external drive, it might get prioritized too) that is "writable" (in case of a protected drive).... To avoid the external drive issue, we could also consider saving in a specific folder (so if it's missing, it's not the right drive...)... anyway, there are plenty of options (most likely passing through WMI as michel_m suggests)... let us know....
--
A beautiful solution is understood as the simple and easy solution to a difficult and complicated problem.
Hello,
I tried Michel_m's code but it gets stuck here
Wscript.Echo "DeviceID: " & objDisk.DeviceID
Moreover, my drive is in G but it could also be in F..........so what can I do?
I'm stuck here!
Thanks in advance
waea
I tried Michel_m's code but it gets stuck here
Wscript.Echo "DeviceID: " & objDisk.DeviceID
Moreover, my drive is in G but it could also be in F..........so what can I do?
I'm stuck here!
Thanks in advance
waea
Hello,
if you want to use Michel_m's code, use the first one, the second one is a VBS...
Have you looked at the link in my post 6? Can you please answer these questions (basically how to identify the USB key, because there are many types of "removable drives" in Windows that don't differentiate....)?
--
A beautiful solution is understood as the simple and easy solution to a difficult and complicated problem.
if you want to use Michel_m's code, use the first one, the second one is a VBS...
Have you looked at the link in my post 6? Can you please answer these questions (basically how to identify the USB key, because there are many types of "removable drives" in Windows that don't differentiate....)?
--
A beautiful solution is understood as the simple and easy solution to a difficult and complicated problem.
Hello,
after trying the 1st code, it gives me a dialog box with F recognized for my inserted USB key.
but I can't do anything more..........neither copy the workbook with macros nor anything at all!
help me please!
waea
after trying the 1st code, it gives me a dialog box with F recognized for my inserted USB key.
but I can't do anything more..........neither copy the workbook with macros nor anything at all!
help me please!
waea
Re,
you still haven't answered my questions....your problem is poorly stated (or not completely)! What do we do if multiple USB drives are connected (multiple 'removable disks'), how do we identify the drive to save on, do we just pick any of them (at the risk of writing on an external disk or an SD card or something else)? Then, if you want us to write all the code for you, I would tend to say don’t push it, do a little research and you should be able to find the code to save (at the same time, I’m being nice today, if you answer the questions maybe I’ll do that for you...)
"help me please!" => well, you see, I'm doing what I can but it's you who isn't helping me to help you :p
--
A good solution is understood as the simple and easy solution to a difficult and complicated problem.
you still haven't answered my questions....your problem is poorly stated (or not completely)! What do we do if multiple USB drives are connected (multiple 'removable disks'), how do we identify the drive to save on, do we just pick any of them (at the risk of writing on an external disk or an SD card or something else)? Then, if you want us to write all the code for you, I would tend to say don’t push it, do a little research and you should be able to find the code to save (at the same time, I’m being nice today, if you answer the questions maybe I’ll do that for you...)
"help me please!" => well, you see, I'm doing what I can but it's you who isn't helping me to help you :p
--
A good solution is understood as the simple and easy solution to a difficult and complicated problem.
Hello and I'm sorry if I offended you.............I hadn't taken the time to fully understand your requests!
Actually, there is only one key to connect and it can either have the letter F or the letter G in the case of another key that has a part where I can input data.
I didn't realize that the letter of the key could be important.
If it's easier to have just one letter, then we should choose G.
I had indeed found a code that allowed me to transfer the file, but I couldn't open it afterwards from the said key???????????
I was told that the file was not recognized.............so my very simple code does not work. That's why I'm reaching out to you.
I realize that I'm asking a lot from you and if you don't want to continue, I would totally understand!
You've already done a lot
Do these pieces of information suit you?
Best regards
Waea
Actually, there is only one key to connect and it can either have the letter F or the letter G in the case of another key that has a part where I can input data.
I didn't realize that the letter of the key could be important.
If it's easier to have just one letter, then we should choose G.
I had indeed found a code that allowed me to transfer the file, but I couldn't open it afterwards from the said key???????????
I was told that the file was not recognized.............so my very simple code does not work. That's why I'm reaching out to you.
I realize that I'm asking a lot from you and if you don't want to continue, I would totally understand!
You've already done a lot
Do these pieces of information suit you?
Best regards
Waea
Re, no worries, I'm not "offended," maybe just a bit frustrated. I feel like the solution is close, but we're missing some elements and can't get there...
So, let me explain again, the difficulty here is to be sure to save on the right "removable disk" because that’s how Windows recognizes USB sticks but also a bunch of other things like, for example, a hard drive or even an MP3 player or a camera in some cases... So we need to find a way to identify the USB stick to save on, in case multiple devices are connected. This could be the name of the USB stick, a file, or a folder already present on the stick, or something else...
I suggest this:
1 - You need to identify the name of your USB stick (what shows up in My Computer). If it doesn't have a name ("removable disk"), give it one.
2 - Then paste this code into your Excel file, adapting the name of your USB stick (in my case, I named it USB TOM):
If the solution with the name doesn’t work, we’ll need to find another way to make sure we’re on the right USB device (presence of a file/folder?)....
--
By a nice solution, we mean the simple and easy solution to a difficult and complicated problem.
So, let me explain again, the difficulty here is to be sure to save on the right "removable disk" because that’s how Windows recognizes USB sticks but also a bunch of other things like, for example, a hard drive or even an MP3 player or a camera in some cases... So we need to find a way to identify the USB stick to save on, in case multiple devices are connected. This could be the name of the USB stick, a file, or a folder already present on the stick, or something else...
I suggest this:
1 - You need to identify the name of your USB stick (what shows up in My Computer). If it doesn't have a name ("removable disk"), give it one.
2 - Then paste this code into your Excel file, adapting the name of your USB stick (in my case, I named it USB TOM):
Sub usbtransfer() strComputer = "." Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}\\" & strComputer & "\root\cimv2") Set colDisks = objWMIService.ExecQuery _ ("SELECT * FROM Win32_LogicalDisk WHERE DriveType = 2", , 48) For Each objdisk In colDisks If objdisk.VolumeName = "USB TOM" Then rdrive = objdisk.deviceid & "\" End If Next objdisk ActiveWorkbook.SaveAs rdrive & ActiveWorkbook.Name End Sub If the solution with the name doesn’t work, we’ll need to find another way to make sure we’re on the right USB device (presence of a file/folder?)....
--
By a nice solution, we mean the simple and easy solution to a difficult and complicated problem.
Re,
yes, but we can avoid displaying this message by replacing:
with:
--
By a beautiful solution, we mean the simple and easy solution to a difficult and complicated problem.
yes, but we can avoid displaying this message by replacing:
ActiveWorkbook.SaveAs rdrive & ActiveWorkbook.Name
with:
Application.DisplayAlerts = False ActiveWorkbook.SaveAs rdrive & ActiveWorkbook.Name Application.DisplayAlerts = True
--
By a beautiful solution, we mean the simple and easy solution to a difficult and complicated problem.
Hello again,
Actually, while trying your code everything went well on the key, but then I closed Excel and upon restarting Windows, I received a blue screen indicating an unexpected shutdown of Windows.
I had to choose an option: safe mode, etc.... normal Windows
Can you explain to me what this means?
For me, this is likely due to the procedure which probably poses a little problem for Windows.
Perhaps there's an additional manipulation I need to do?
Thank you in advance
Waea
Actually, while trying your code everything went well on the key, but then I closed Excel and upon restarting Windows, I received a blue screen indicating an unexpected shutdown of Windows.
I had to choose an option: safe mode, etc.... normal Windows
Can you explain to me what this means?
For me, this is likely due to the procedure which probably poses a little problem for Windows.
Perhaps there's an additional manipulation I need to do?
Thank you in advance
Waea
Hello,
A small piece of advice, when working with a USB drive, before removing it, it is recommended to go through the computer and right-click on the relevant device and click [Eject].
Try it out to see if the problem is at that level.
--
Regards.
Jean-Pierre
A small piece of advice, when working with a USB drive, before removing it, it is recommended to go through the computer and right-click on the relevant device and click [Eject].
Try it out to see if the problem is at that level.
--
Regards.
Jean-Pierre
Hello Waea, Jean-Pierre,
I think like Jean-Pierre; it may be a problem with using the USB key.... As for the VBA procedure, I don't see what could be causing this issue. I've tested it several times and I just tried it on another machine (running Vista), no problem.... Moreover, I have been using this kind of procedures in VBA/VBS for quite a while, and I've never observed this behavior...
Have you done a new test since? Does the problem occur every time you use the macro?
--
A beautiful solution is understood as the simple and easy solution to a difficult and complicated problem.
I think like Jean-Pierre; it may be a problem with using the USB key.... As for the VBA procedure, I don't see what could be causing this issue. I've tested it several times and I just tried it on another machine (running Vista), no problem.... Moreover, I have been using this kind of procedures in VBA/VBS for quite a while, and I've never observed this behavior...
Have you done a new test since? Does the problem occur every time you use the macro?
--
A beautiful solution is understood as the simple and easy solution to a difficult and complicated problem.
Hello again,
first of all, I tried again and it works: actually, I have a key that has integrated software requiring a password and a menu. I had clicked to close the key via the integrated program, but apparently that’s not enough!
I also need to go through the Windows eject menu!
However, I tried with another key that I also named PRI (for private), which this time is in F: (whereas the previous key was in G:); the file saves as well, but in Windows Explorer I don’t have the version of the copy (date time) of the save?
The file also seems to open, but it would be handy to have this information!
Can you please explain this to me?
Thank you in advance
waea
first of all, I tried again and it works: actually, I have a key that has integrated software requiring a password and a menu. I had clicked to close the key via the integrated program, but apparently that’s not enough!
I also need to go through the Windows eject menu!
However, I tried with another key that I also named PRI (for private), which this time is in F: (whereas the previous key was in G:); the file saves as well, but in Windows Explorer I don’t have the version of the copy (date time) of the save?
The file also seems to open, but it would be handy to have this information!
Can you please explain this to me?
Thank you in advance
waea
I just tried this procedure and in my case I always receive the letter
[A:] while the key is located in [G:]!
What’s the trick?
--
Best regards.
Jean-Pierre
michel_m's code is correct, it detects the first "removable disk" (drivetype = 2) connected (but it can be something else, Windows doesn't really distinguish between an external drive, a memory card reader, or even a floppy disk drive connected via USB - it's all 'removable disk')... the question that arises is what this drive A: corresponds to on your machine?
--
By a beautiful solution, we mean the simple and easy solution to a difficult and complicated problem.
Thank you immensely for the explanation that reinforces what I have discovered in the meantime.
--
Best regards,
Jean-Pierre
For our IT culture
vbscript (found on the internet from the list of drivers). The component must be installed to be taken into account
strComputer = "." Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") Set colDisks = objWMIService.ExecQuery _ ("Select * from Win32_LogicalDisk") For Each objDisk in colDisks Wscript.Echo "DeviceID: "& objDisk.DeviceID Select Case objDisk.DriveType Case 1 Wscript.Echo "No root directory. Drive type could not be " _ & "determined." Case 2 Wscript.Echo "DriveType: "& "Removable drive." Case 3 Wscript.Echo "DriveType: "& "Local hard disk." Case 4 Wscript.Echo "DriveType: "& "Network disk." Case 5 Wscript.Echo "DriveType: "& "Compact disk." Case 6 Wscript.Echo "DriveType: "& "RAM disk." Case Else Wscript.Echo "Drive type could not be determined." End Select NextSo at home the USB key is on drive G: and the external hard drive is on H:
--
:-x
"the component must be installed," which component are you talking about?
--
By a beautiful solution, we mean the simple and easy solution to a difficult and complicated problem.