Forum Discussion
Question mark + added (2) in query name
Hi everyone,
I'm currently working with some .xlsb and .xlsm files with PQ. I do basically pull data from OData feeds and OLE DB connections, rearange everything, and run a macro to shoot emails. All files/connections pointing to these OLEDB/Odata work properly, however, I do have a couple of files which everytime I open them, a question mark and a (2) is added in query names. See picture:
- First query (01 Stock DF) is an Odata feed - only data connection.
- Second query (01 PG) is an excel file - only data connection.
- Third query (01 Projeccio...) is an OLE DB connection - loaded in a table/excel tab.
- I changed permission from public to organizational for all connections. This doesn't seem to fix the issue.
When I rollover with mouse, queries refresh properly. Plus, table name for third query (the one I load into an excel tab) also changes name (adding "__2" at the end of previous table name). However, having query connections with changed name and question mark crashes my VBA, and I need to setup again type of data load (only connection/table/pivot, etc.) and I guess this should not happen.
Any thoughts on this?
Thanks for your support,
Hi lbendlin ,
I've been doing try and error. Definetely nothing to do with my VBA code.
I realized this excel file with queries where made by doing a copy/paste from another excel file queries. This ended up in having same query duplicated in this new excel file.
I started from scratch, creating all connections, writing all M... and voila, it works now.
Thanks for your support.
Regards,
5 Replies
Your macro code seems to attempt to recreate connections that already exist. Maybe modify your code to first look if a connection exists before attempting to create it.
Hi lbendlin,
thanks for your answer. In order to find solution:
- I've saved one of these two files which are not working properly as .xlsb, not .xlsm. All my other Excel files with PQ are saved as .xlsb and work flawlessly.
- My macro code refreshes query in this way:
- And like other files, "Enable background refresh" from query I load into an excel tab is not ticked:
Tomorrow will refresg again query, but right click on table > refresh.
Will check tomorrow again and come back with results.
Thanks,
Can you provide sanitized sample VBA code that illustrates the issue?
