Working outlook via vba code with xlsx - error

[luma64]

Haloo ! I am writing in outlook 2007 macro what si working with xlsx. In some row ic code is error

Sub WriteToExcelFile()

Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlWorksheet As Object
Dim filePath As String

On Error Resume Next 'firstly, try catching the existing open session, if any:
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then 'if no any existing session, create a new one:
Err.Clear: Set xlApp = CreateObject("Excel.Application")
MsgBox "Object created"
End If
On Error GoTo 0

' Set the file path of the Excel file
filePath = "c:\moje_dokumenty\E-Recept\data.xlsx"

' Create a new instance of Excel
' Open the Excel file

'On row is mistake "call was reejected calee"
Set xlWorkbook = xlApp.Workbooks.Open(filePath)

'other code write with sheet in c:\moje_dokumenty\E-Recept\data.xlsx

' Check if the file exists
If Dir(filePath) <> "" Then
MsgBox "The file exists."
Else
MsgBox "The file does not exist."
End If
.

End sub
'Version Office 2007
'References in Outlook and Excel Visual basic Application:
'Microsoft Office Excel 12.Object Library
'Microsoft Excel 12.Object Library

How can I solve my problem ? Thanks for help !

 

[Anthony47]

To avoid misunderstandings, could you describe what you are trying to achieve, that a file exists with path & name = "c:\moje_dokumenty\E-Recept\data.xlsx", and which error you get

 

[luma64]

Aby ste sa vyhli nedorozumeniam, mohli by ste popísať, čo sa snažíte dosiahnuť, že súbor existuje s cestou a názvom = "c:\moje_dokumenty\E-Recept\data.xlsx" a ktorá chyba sa vám zobrazuje

All my code is written in Outlook VBA. It means go to some subfolder , read every mail and write Subject, Received etc and information write fo specify xlsx file (c:\moje_dokumenty\E-Recept\data.xlsx).
First I need find out if this file exist. If it is exist - close this. But when I try use Set xlWorkbook = xlApp.Workbooks.Open(filePath) then I get error message "call was reejected calee".

 

[Anthony47]

If the first problem is to determine the status of that file then I suggest using the following "function":

Function FileStatus(filename As String) As Variant
'Check file status; codice di ritorno:
'0=file is free, 70=file is busy, 53=file doesn't exist
'76=path doesn't exist
'altri errori: to evaluate
'
    Dim filenum As Integer, errnum As Integer
'
    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
FileStatus = errnum
End Function

You call it for example using

fStat = FileStatus("D:\Path\FileName.ext")

then examine the value for fStat; main results:

'0=file is free, 70=file is busy, 53=file doesn't exist
'76=path doesn't exist

If the file is busy you need to inform the owner to close it

Try...