Hello guys,
Sorry for long post... Here is my problem:
I have prepared some parametrised (2/3 parameters) ODBC queries and they are fully functional when I refresh them manually. And they are also fully functional when I run my refreshing code by F8 method.
But once I execute whole code by F5 - parameters gets lost. I tell excel to fill cells with parameters with values I want (for example 201904 or 201910). At this point code and queries are fine.
It 's ignored only during full compilance. But not in some various ways. My parameter for project ID is fine but changes parameter for date. Even when I tell him to refresh for 201904 it is done for 201912 in every case.
Every workbook and every query. If it was dependant on value of loop "i" it would get bigger with every next workbook. I also checked the whole code very carefully and it seems fine to me.
Do I really have to rewrite query to ODBCConnection.Command = [...] or there is a way to make excel look for the parameters as usual?
Heres one of SQL queries (adjusted for sharing in web) that is refreshed. As you see there is 3 parameters that refers to cells in a proper way and should by fine with changing its' velues by previous macro.
select
a.account_no KONTO,
'' as "RODZAJ",
[...].Get_Description(company,account_no,'CODE_A') OPIS_KONTA,
a.project_no PROJEKT,
b.part_no NR_POZYCJI,
[...].Get_Description(b.contract,b.part_no) Nazwa_pozycji,
b.quantity ILOSC,
[...].Get_Unit_Meas(b.contract,b.part_no) JM,
a.value WARTOSC,
a.ACCOUNTING_YEAR Rok_księgowania,
--nvl(a.ACCOUNTING_YEAR, extract(YEAR FROM SYSDATE)) Rok_księgowania,
--nvl(a.ACCOUNTING_PERIOD, extract(MONTH FROM SYSDATE))Okres_księgowania,
a.ACCOUNTING_PERIOD Okres_księgowania,
a.ACTIVITY_SEQ Id_działania
--a.date_applied
from [...]_accounting a, [...]_transaction_hist b
where
a.company='gggg' AND
a.account_no like '5%'
and a.account_no not like '552%'
and a.accounting_id=b.accounting_id
and UPPER(a.project_no)like UPPER('%' || ? || '%') and (this parameter seems to be fine but it is constant in every different workbook, it isnt changed by macro)
SUBSTR(a.project_no,1,4)!='xxxx' AND SUBSTR(a.project_no,1,4)!='xxyy'
AND a.project_no!='cccc' AND SUBSTR(a.project_no,LENGTH(a.project_no),1)!='U'
and a.date_applied>=? (in cell equals 2019-01-01 but refreshes for 2019-12-01)
and a.date_applied<=? (in cell equals 2019-01-31 but refreshes for 2019-12-31)
order by 1
Bookmarks