How do I refer to the value returned by this function?

[qjd2004]

Hiya,

I've got a function that checks to see what kind of drive a drive letter refers to (i.e. HDD or CD-ROM, etc).

My macro saves to a location on a HDD, I want to specify which location depending on whether there is a fixed HDD called "D:\" or not. But I don't know how to refer to the value returned by the DriveType function in my procedure. Can someone help?

My Code:

Option Explicit

Private Declare Function GetDriveType Lib "kernel32" _
  Alias "GetDriveTypeA" (ByVal nDrive As String) As Long

Function DriveType(DriveLetter As String) As Integer
'Returns an integer that defines the type of drive of DriveLetter
Dim x As Integer

   DriveLetter = "D:\"                              'NOT IN USE: Left(DriveLetter, 1) & ":\"
   Select Case GetDriveType(DriveLetter)
      Case 0: DriveType = 0                 'Unknown
      Case 1: DriveType = 1                 'Non-existent
      Case 2: DriveType = 2                 'Removable drive
      Case 3: DriveType = 3                 'Fixed drive
      Case 4: DriveType = 4                 'Network drive
      Case 5: DriveType = 5                 'CD-ROM drive
      Case 6: DriveType = 6                 'RAM disk
      Case Else: DriveType = 999            'Unknown drive type
   End Select
End Function

Public Sub ExtractOutput()

Dim MyXL As Excel.Application
Dim ws As Worksheet                                             
Dim LastRow As Long                                             
Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell)    
'LastRow = LastCell.Row
Dim MyRange As String
Dim x As Integer
Dim fn As String
Dim r As Variant
Dim cl As Object
Dim fileSaveName
'MyRange = "D14:E" & LastRow
LastRow = 0   

	'Specify whether there is a "D:\" drive and if so save to location, else save to another location
	If GetDriveType = 3 Then
	
		'Save the file to "D:\Data\RunXls" so it can be refered to in other procedures
		ActiveWorkbook.SaveAs Filename:="D:\Data\RunXL\Extract.xls", FileFormat:=xlNormal
	Else
		'Save the file to "C:\RunXls" so it can be refered to in other procedures
		ActiveWorkbook.SaveAs Filename:="C:\RunXL\Extract.xls", FileFormat:=xlNormal
	End If

etc.......

Please can someone help and explain how this works for future reference?

Many thanks,

Q

 

[VulcanJedi]

you need to call the function w/ the argument. If the function returns a value such as yours you'll need to store in a variable.
If there was a square root function
ie:
myVariable = SQUARE(25)

IF myVariable = 5 then
....
end if...


Tranpkp

 

[MakeItSo]

In you r function, you have a line

DriveLetter = "D:\"

Comment this out first. Then you can get the function result from any of your modules like this:

If DriveType("D:\")=3 then
 store to D:\...
Else
 ...

Is this what you need?
MakeItSO

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[combo]

You can either simplify your function (and next use it as in above procedure):
[tt]Function DriveType() As Integer
'Returns an integer that defines the type of "D:\" drive
Select Case GetDriveType("D:\")
Case 0: DriveType = 0 'Unknown
Case 1: DriveType = 1 'Non-existent
...[/tt]

or extend its functionality:
[tt]Function DriveType(DriveLetter As String) As Integer
'Returns an integer that defines the type of drive of DriveLetter
Select Case GetDriveType(DriveLetter)
Case 0: DriveType = 0 'Unknown
Case 1: DriveType = 1 'Non-existent
...[/tt]

and use it with argument:
[tt]If GetDriveType("D:\") = 3 Then[/tt]

combo

 

[qjd2004]

Thank you guys,

that's a great help. I'm getting the idea now!.