Check if excel file is open, if yes close file,if no convert csv file to excel Visual Basic

Question

I'm having a problem creating a condition. Please see pseudo code below. thanks in advance

Check if File A.xls is open If File A.xls is Open

    Close File A.xls
Else

    Convert File A.csv to .xls

End If

Convert File A.csv to .xls

Dim DeleteEntries As Workbook
Dim WorksheetDeleteEntries As Worksheet
Dim WbOpen As Boolean

'Convert Acc_FR044_SAP.csv to excel
strDir = "C:\FR044 Automated Checker\"
strFile = Dir(strDir & "Acc_FR044_SAP.csv")

If Workbooks("Acc_FR044_SAP.xls") Is Nothing Then ' IM HAVING AN SUBSCRIPT ERROR IN THIS LINE

    WbOpen = False

Else
    Workbooks("Acc_FR044_SAP.xls").Close SaveChanges:=False

End If


Application.DisplayAlerts = False
Do While strFile <> ""
Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True)
wb.SaveAs Replace(wb.FullName, ".csv", ".xls"), FileFormat:=xlExcel8
wb.Close True

Set wb = Nothing
strFile = Dir
Loop

It prompts an subscript error on this line. If Workbooks("Acc_FR044_SAP.xls") Is Nothing Then — Solark, Jun 16 '15 at 06:49
if that workbook is closed you can't check it existence in this way `Workbooks("Acc_FR044_SAP.xls"` as there is any object of that type & name. You can try solution provided below in the answer — Kazimierz Jawor, Jun 16 '15 at 06:55

Kazimierz Jawor · Accepted Answer · 2015-06-16T09:50:28.677

2

Try with this solution which works for current instance of Excel:

On Error Resume Next
Dim tmpWB As Workbook
Set tmpWB = Workbooks("Acc_FR044_SAP.xls")
On Error GoTo 0

If tmpWB Is Nothing Then 

    WbOpen = False

Else
    tmpWB .Close SaveChanges:=False

End If

edited Jun 16 '15 at 09:50

answered Jun 16 '15 at 06:54

Kazimierz Jawor

18,861
7
35
55

2

This method works; No Doubt. However it is unreliable if the workbook is opened in a different excel instance like @brettdj mentioned :) – Siddharth Rout Jun 16 '15 at 09:05

score 2 · Answer 2 · edited May 23 '17 at 11:43

2

Something like this to check if the file was open in any instance, on any machine

Sub Sample()
    Dim bFileOpen As Boolean
    bFileOpen = IsWorkBookOpen("C:\yourfilename.xlsx")

    If bFileOpen Then
        MsgBox "File is open"
    Else
        MsgBox "File is Closed"
    End If
End Sub

testing function from Microsoft example here

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long
    Dim ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
        Case 0
    IsWorkBookOpen = False
        Case 70
    IsWorkBookOpen = True
    Case Else
    End Select
End Function

edited May 23 '17 at 11:43

Community

1
1

answered Jun 16 '15 at 08:39

brettdj

54,857
16
114
177

downvoted as no one is asking for such solution and your answer in too sophisticated to simple OP's problem. – Kazimierz Jawor Jun 16 '15 at 08:50
1

++ A more reliable way of checking. I also use the same logic as shown [HERE](http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba/9373914#9373914) – Siddharth Rout Jun 16 '15 at 09:04
What does 'OP' mean ? – iDevlop Jun 16 '15 at 09:05
@iDevlop: Depends on how you use it. `Original Poster`. `Original Post` – Siddharth Rout Jun 16 '15 at 09:07
Sorry, English is not my native language but where in the question you find something like `if file is open on any instance of Excel or anywhere?`. What I see in question is default and most common situation - `if file is open on my current Excel instance` and it seems that I was right with my answer. Indeed, you did a blindly downvoting and indeed you are right with your answer which I know and use in situations where necessary. There is no need to use it here if you have simple solution available. Don't you think we could be both right? – Kazimierz Jawor Jun 16 '15 at 09:32
@KazimierzJawor your code certainly works fine on the current instance, but as current instance wasn't actually specified, and the code I provided hardly *complex* then why would you look for a robust solution rather than a partial one. If you edit your code (so I can change my vote) then I will remove my down vote given you have taken the trouble to explain why you posted as you did. Cheers. – brettdj Jun 16 '15 at 09:38

Check if excel file is open, if yes close file,if no convert csv file to excel Visual Basic

2 Answers2