Formula.Firewall Issue

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,406
I have an Excel file that uses Power Query to get data from two Access tables.

The query is executed via the following VBA code in the Workbook_Open code
Code:
ActiveWorkbook.Connections("Query - SourceFileName").Refresh
ActiveWorkbook.Connections("Query - GoodsOut").Refresh
The SourceFileName query has three steps
1 - = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content] - Where 'tblFilePath' holds the location of the Access database
2 - = Table.TransformColumnTypes(Source,{{"File_Path", type text}})
3 - = #"Changed Type"{0}[File_Path]

This is then referrred to for two other queries to get data from two tables in Access
= Access.Database(File.Contents(SourceFileName&"\StockMovement.accdb"), [CreateNavigationProperties=true])

When I have to replace the current Excel file that holds the above with an updated file, I get the Formula.Firewall message when the query that refers to the SourceFileName tries to execute.

To resolve this I have to go into the Query Settings and change the Privacy level to ignore for the workbook and then save the file.

As the file can be used as read-only, if it is open and someone else opens it, the VBA bugs out on
Code:
ActiveWorkbook.Connections("Query - SourceFileName").Refresh
ActiveWorkbook.Connections("Query - GoodsOut").Refresh
It's becoming more of an issue for me as I am building more files that uses PQ and ideally I'd like to implement a fix rather than having to manually set the privacy levels as and when it bugs out.

TIA
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,406
Below is the Advanced Editor code which might make my issue a bit clearer

Getting the location of the data in the Access file
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="tblFilePath"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File_Path", type text}}),
    Source_Data_File_Path = #"Changed Type"{0}[File_Path]
in
    Source_Data_File_Path
Refreshing the file with the required data in access
Code:
let
    Source = Access.Database(File.Contents(SourceFileName&"\StockMovement.accdb"), [CreateNavigationProperties=true]),
    _GoodsIn = Source{[Schema="",Item="GoodsIn"]}[Data]
in
    _GoodsIn
 

Forum statistics

Threads
1,078,352
Messages
5,339,715
Members
399,320
Latest member
sut3k

Some videos you may like

This Week's Hot Topics

Top