FileDialogOpen with Excel

[diff 1]

Hi,

 

was searching in the forum for solution but didn't found anything similar, so my problem is that after user selects Excel file the other _Excel_ functions doesn't work.

 

I created a logic, that if any user changes file name or file location then has option to select the file location manually with FileDialogOpen

 

This is how it looks:

$fileLocation = "C:\Users\Desktop\File.xlsx"

If Not FileExists($fileLocation) Then
    MsgBox(48, "File not found", "File not found, please select")
    FileOpenDialog("Select file", "C:\Users\", "Excel (*.xlsx*)")
EndIf

    $oExcel = _Excel_Open(1, 1, 1, 1, 1)
    $xlBook = _Excel_BookOpen($oExcel, $fileLocation)
    $xlBook.Sheets("Users").Activate 
    
    .....and continues other $xlBook.Sheets functions.......

 

So if the File.xlsx is found on this location everything works correctly the sheet .Active works as expected, but if the File.xlsx not found and user selects in other directory same file, then all my $xlBook.Sheets functions doesn't work and crashes on first one which is $xlBook.Sheets("Users").Activate 

 

What did I missed?

 

Thank you for the help

 

[seadoggie01 301]

Well... $fileLocation never changes. You probably meant to set it equal to the result of the FileOpenDialog 

[Nine 1,606]

If you had put some error handling after each statements, you would have found out what was the problem.  Look in help file under Excel UDF to learn how to insert messages that check for errors (in all examples there is error handling).

Edited August 4, 2020 by Nine