Hi David,
One way is to try to open it exclusively yourself. Air code:
Function IsFileAvailable(FileSpec as String) As Boolean
If Len(Dir(FileSpec)) > 0 Then 'make sure file exists
lngFileNum = FreeFile()
On Error Resume Next
Open FileSpec For Input Lock Read Write As #lngFileNum
If Err.Number = 0 Then 'file opened successfully
IsFileAvailable = True
Close #lngFileNum
Else 'file could not be opened
IsFileAvailable = False
End If
On Error GoTo 0
End If
End Function
As well as checking that the file exists, you may also want to check the
permissions on it; if you don't, the function will misleadingly return
True when the file isn't actually open but the current user doesn't have
permission to open it.
Also, remember that the fact that a file is "open" in some application
does not necessarily mean that the file is open from the operating
system's point of view. When Notepad (and many others) "open" a text
file, for instance, they open the actual disk file, read its contents
into memory, and immediately close it. The actual editing is done in
memory and the file on disk isn't touched until you save your work. As
far as I know, however, Word and Excel keep their files open.
On Thu, 1 Jun 2006 11:56:03 -0700, David
<David[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >Hello,
>I have this bit of code. The transfer spreadsheet command does not appear
>to be returning all of the data properly if the excel file is open when the
>command is executed.
>Does anyone know how I can check if the selected file is open so I can run
>that check before i execute the transfer spreadsheet command?
>
>**** CODE SAMPLE
>Private Sub cmd_Import_MTT_Click()
>10 On Error GoTo cmd_Import_MTT_Click_Error
>
>Dim strFilter As String
>Dim strInputFileName As String
>Dim tempCount As Integer
>Dim Response As String
>
>'this will prompt user to select excel file to import
>20 strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
>"*.XLS")
>30 strInputFileName = ahtCommonFileOpenSave( _
>Filter:=strFilter, OpenFile:=True, _
>DialogTitle:="Please select an input file...", _
>Flags:=ahtOFN_HIDEREADONLY)
>
>40 Me.txt_InputFile = strInputFileName
>47 DoCmd.OpenForm "frm_Processing"
>
>48 Forms!frm_Processing.Refresh
>49 Forms!frm_Processing.Repaint
>
>
>50 DoCmd.SetWarnings False
>
>'add rows from selected spreadsheet Requirement Info tab to
>temp_Excel_Requirement_Info-0 table
>
>180 DoCmd.TransferSpreadsheet acImport, ,
>"temp_Excel_Requirement_Info-0", strInputFileName, False, "Req & BTS
>Info$A:Z"
>'*** END OF CODE SAMPLE
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|