Where did you put the code for FirstRemovable? It needs to be in the
same module as the code from "The Access Web", or else you have to go
into the module containing the code from the web and change
Private Function fDriveType(strDriveName As String) As String
to
Public Function fDriveType(strDriveName As String) As String
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I have followed your suggestions to a T. I did copy the module and your
code. When I click my cmd button I get "Complie error, sub or function
not defined, when debugged the "Function First Removable" is highlighted
at "Select Case fdrive type" I appreciate your help...Randy
message Looks as though you didn't make any changes to your original code, as
though you didn't read the suggestion I gave you at all!
You say you copied the code from
http://www.mvps.org/access/api/api0003.htm into a module. Did you also
copy the additional code for the FirstRemovable() function I gave you?
Assuming you did, change your code to:
Private Sub ImportFlashStick_Click()
DoCmd.TransferSpreadsheet acImport, 8, "AnalysisForm",
FirstRemovable() & Me.List3, True, ""
End Sub
As I said before, you may run into problems with this if a particular
user has more than one removable drive installed.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Heres my code in my macro, file Name to transfer data:
="F:\" & [Forms]![ExportToFlashStick]![FileName5] & ".xls" Heres my
code to imports data via cmd button on my form:
Private Sub ImportFlashStick_Click()
DoCmd.TransferSpreadsheet acImport, 8, "AnalysisForm", "F:\" &
Me.List3, True, ""
End Sub
Like I said, the directory is not always "F"
message Sorry, but "couldnt get it to work" doesn't give much to go on.
Do you get an error? If so, what is it? If there's no error, what
result do you get, and what should you get?
Including the code you're using would help a lot too...
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I couldnt get it to work. I have a form where I enter the file name
to be exported, the beginning date and ending date. I have a cmd
button to run a macro that transfers data to the f drive
(Flashstick) but not all computors has the F drive as the reoveable
drive. I need a way that when you click the cmd button, something
asks for the drive location of the flashstick and once I click on
the correct location the file is transferred. Thank you...
Thank you, I'll give it a try. Thanks for your response...Randy
message Open the Debug Window (Ctrl-G), type sListAllDrives and hit
enter. You should get a list of all of your drives. For instance,
here's what I get if I don't have a thumbdrive attached:
sListAllDrives
Local drive : C:\
Local drive : D:\
CD Rom drive : Z:\
And when I do have a thumbdrive:
sListAllDrives
Local drive : C:\
Local drive : D:\
Removable drive : F:\
CD Rom drive : Z:\
Now, you could write A function that loops through and returns
the first Removable Drive it finds, along the lines of
Function FirstRemovable() As Variant
Dim strAllDrives As String
Dim strTmp As String
FirstRemovable = Null
strAllDrives = fGetDrives
If strAllDrives <> "" Then
Do
strTmp = Mid$(strAllDrives, 1, InStr(strAllDrives,
vbNullChar) - 1)
strAllDrives = Mid$(strAllDrives, InStr(strAllDrives,
vbNullChar) + 1)
Select Case fDriveType(strTmp)
Case "Removable Media":
FirstRemovable = strTmp
Exit Do
End Select
Loop While strAllDrives <> ""
End If
End Function
However, recognize that if they've got more than one removable
drive attached, that may not result in what you want.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Iv'e copied the code to a module. Where do I go from here? How
do I run the module?..
message Take a look at
http://www.mvps.org/access/api/api0003.htm at
"The Access Web"
It should show up as "Removable Drive"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Access 2000. I have 10 laptops with an identical db. I'm
trying to export and import files via Flashstick from the
laptops to my main db. I have this code to export files to my
flashstick. The prolem is that the drive the flashstick is on
is not always "F", it varys from computor to computor.How can
I check which drive the flashstick is on prior to exporting
the files? Heres my code in the macro. ="F:\" &
[Forms]![ExportToFlashStick]![FileName5] & ".xls"
Here's whatI have in a cmd button on my form to import.
Private Sub ImportFlashStick_Click()
DoCmd.TransferSpreadsheet acImport, 8, "AnalysisForm", "F:\" &
Me.List3, True, ""
End Sub
