Open excel without update

[Vagelisr]

Hi to all

I use this code to open multiple Excel files and taske the vaslue of a specific cell

Function OpenExcelFile(PathFolderName) As String
    Dim XL As Excel.Application
    Dim WBK As Excel.Workbook
    Set XL = CreateObject("Excel.Application")
    Set WBK = XL.Workbooks.Open(PathFolderName)
    OpenExcelFile = WBK.Sheets(TabName).Range(CellName).Value
    WBK.Close False
    Set XL = Nothing
End Function

In one of them it show me the message "This workbook contains links to other data sources"

I'm not sure aboute the message because is in Grrek language.
Did you knou how can i open this excel without show me any messages about update???

Thanks and regards.

 

[GTO]

Greetings,

Is this code housed in an Excel Workbook? If so, I am curious as to why you creating a new instance of the application each time the function is called?

Disregarding that for the moment and more to your question, have you tried including the arguments to open the file read-only and without updating links? Something like:

Function OpenExcelFile(PathFolderName) As String
Dim XL As Excel.Application
Dim WBK As Excel.Workbook


  Set XL = CreateObject("Excel.Application")
  Set WBK = XL.Workbooks.Open(PathFolderName, UpdateLinks:=False, ReadOnly:=True)
  OpenExcelFile = WBK.Sheets(TabName).Range(CellName).Value
  WBK.Saved = True
  WBK.Close False
  Set XL = Nothing
  
End Function

Hope that helps,

Mark

 

[gallen]

Something like this, note that I've added error handling to ensure alerts are re-enabled. Added lines shown in red

Function OpenExcelFile(PathFolderName) As String
    Dim XL As Excel.Application
    Dim WBK As Excel.Workbook
[COLOR=#ff0000]On Error GoTo errHandle[/COLOR]
    Set XL = CreateObject("Excel.Application")
[COLOR=#ff0000]    Application.DisplayAlerts = False[/COLOR]
    Set WBK = XL.Workbooks.Open(PathFolderName)
    OpenExcelFile = WBK.Sheets(TabName).Range(CellName).Value
    WBK.Close False
[COLOR=#ff0000]    Application.DisplayAlerts = True[/COLOR]
    Set XL = Nothing
    
Exit Function
[COLOR=#ff0000]errHandle:[/COLOR]
[COLOR=#ff0000]    Application.DisplayAlerts = True[/COLOR]
End Function

Edit: Actually Mark's is a better solution: ^^^^

 

[Vagelisr]

Thank you very much both of you.
GTO it work like a charm!!!!!!
gallen i haven't add any alerts because i dont need it.
I'm searching in specific folders

Thanksd again

 

[gallen]

gallen i haven't add any alerts because i dont need it.
Thanksd again

The line Application.EnableAlerts = False prevents any application based popups from showing. It must be used with caution as it will prevent things like prompting to save if you haven't thats why you always re-enable immediately after. I've had to use it a few times especially when opening/closing workbooks in VBA

 

[GTO]

Glad we were able to help