Check if folder exists in server (not shared) 2

[suoirotciv]

Good day!

I have this post under SQL thread183-1560051 that is already working.

But since the we need to create a folder at MYserver as a dump location for our excel files for the OPENROWSET to work like D:\ExcelFiles\, I need my program to check first if the folder and files really exists (Note: the application will run at workstations not on server).

Is there a way to check this folder "D:\ExcelFiles\"?

========================================
I kept my Job because of TEK-TIPS
Thanks a lot to all who keeps on helping others.

 

[HughLerwill]

Given that you have the required permissions to create the folder; have you tried something like?

Private Sub Command1_Click()

On Error Resume Next
MkDir "D:\ExcelFiles"
'MsgBox "Error " & Err & " " & Err.Description
'an error 75 is typical if the folder already exists
On Error GoTo 0

End Sub

 

[genomon]

The FolderExists method of the FileScriptingObject also does this quite nicely.

If quizzes are quizzical, what are tests?

 

[suoirotciv]

Thank you for the reply . . . but its not working.

Or maybe I dont explain it well. genomon is right with the code

Public Function DirExists(OrigFile As String)
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
DirExists = fs.FolderExists(OrigFile)
End Function

but my problem is that the folder that i'm going to check is in the server and not on the user's workstation. if i'm going to call the code as:

Call DirExists("\\192.168.1.1\d$\ExcelFiles\")

this will work, because i have full access at the said server. but the user do not have the full access to the server, and our DBA is the one that save the records in the said folder and users do not need to have access on the said files for security purpose. it is only required by SQL's OPENROWSET that the files to be in the local directory where the sql server application was installed.

so i'm just seeking for advice if there is a simple way to check the folder in the server.

thank you and pardon my grammar, i'm not good in english.

========================================
I kept my Job because of TEK-TIPS
Thanks a lot to all who keeps on helping others.

 

[gmmastros]

I would suggest that you do this within SQL Server. There is an undocumented function that you may find very useful.

Here's a SQL Code snippet to help get you started.

Declare @FileName VarChar(1000)
Declare @FileExists Int

SET @FileName = 'D:\ExcelFiles\MyExcelFile.xls'

Exec master.dbo.xp_fileexist @FileName, @FileExists Output

If @FileExists = 1
	Begin
		-- Put your code here
		Select 'File Exists'
	End
Else
	Begin
		-- Put error handling here, or do nothing.
		Select 'File Does Not Exist'
	End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[suoirotciv]

This is great . . thanks a lot . . . Here is what i do

CREATE PROCEDURE dbo.IsFileExists 
		@ExcelFileName VARCHAR(500),
		@IsFileExist INT OUTPUT AS
DECLARE @FileExist INT
Exec master.dbo.xp_fileexist @ExcelFileName, @FileExist Output
SET @IsFileExist = @FileExist
GO

Public Function CheckIfFileExistsInServer(GetFileName As String) As Boolean
   Dim Cmd As New ADODB.Command
   
   Set Cmd = New ADODB.Command
   With Cmd
      Set .ActiveConnection = db
      .CommandTimeout = 0
      .CommandType = adCmdStoredProc
      .CommandText = "IsFileExists"
      .Parameters.Append .CreateParameter("ExcelFileName", adVarChar, adParamInput, 500, GetFileName)    '0
      .Parameters.Append .CreateParameter("FileExist", adInteger, adParamOutput)                         '1
      .Execute
   End With
   If Cmd(1).Value = 1 Then
         CheckIfFileExistsInServer = True
   Else: CheckIfFileExistsInServer = False
   End If
End Function

========================================
I kept my Job because of TEK-TIPS
Thanks a lot to all who keeps on helping others.

 

[MakeItSo]

I would suggest that you do this within SQL Server. There is an undocumented function that you may find very useful.

Hey, this is nice! Didn't know that!
This will sure come in handy some time...


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell