Saving worksheets automtatically - problem

[t16turbo]

hi there,
I have a macro I am running that will run through a list of names, refresh a query using the current name in the WHERE clause, then save the worksheet as <name>.xls

it randonmly returns a run-time error '1004' document not saved

It sometimes happens after running through one iteration, and sometimes after its got through 30!

anyone know why?

 

[xlbo]

how are you refreshing your queries ??

what syntax ??

what is the "BackgroundQuery" property set to ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

hi geoff,
i'm refreshing my queries as per the other thread that you have been helping me with:

    Application.ScreenUpdating = False
    
    '********* Refresh List of Project Managers *********'
    With Sheets("Managers")
        .QueryTables(1).Refresh BackgroundQuery:=False
    
    '********* Run through list of names and use in Query in next sheet *********'
        
        For Each rn In .Range(.[A2], .[A2].End(xlDown))
            sConn = "ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVER=nikulive.dstintl.com;"
            sSQL = "SELECT DSTI_PROJ_REF, DSTI_PDS_PROJMANAGER "
            sSQL = sSQL & "FROM niku.ODF_CA_PROJECT "
            sSQL = sSQL & "WHERE DSTI_PDS_PROJMANAGER = '" & rn.Value & "'"
            
            With Sheets("Sheet4").QueryTables(1)
            .Destination = Sheets("Sheet4").Cells(1, 1)
            .Connection = sConn
            .CommandText = sSQL
            .Refresh BackgroundQuery:=False
           
            End With
            
            Worksheets("Sheet4").SaveAs "c:\" & rn.Value & ".xls"

not sure why it sometimes goes further through the loop than other times...

 

[xlbo]

have you any examples of rn.value where this occurs ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

looking at it, I think there may be a disk space problem, as I tried the process again and the run-time error suggested this.
previoulsy it hasn't mentioned it, so i'll clear enough space and try again!

 

[t16turbo]

What exactly does the Refresh BackgroudQuery function do?

I seem to get a lot of Syntax errors there if I change things in my query.

I have just added a mammoth query:

    '******** Complete Sheet ********'
    sSQL = "SELECT 'Design/Development',x.dsti_proj_ref,p.name ,x.dsti_true_client3 ,x.dsti_prod_grp,x.dsti_product,d.proj_type_name,x.dsti_unapproved_cat,x.dsti_version,x.dsti_proj_likelihood,x.dsti_rad_indicator ,sum(a.practsum/27000),Sum (a.prestsum / 27000)"
    sSQL = sSQL & "FROM niku.odf_ca_project x,niku.nbi_project_current_facts nbi,niku.srm_projects p,niku.dsti_project_analysis d,niku.prtask t,niku.prassignment a,niku.prchargecode c"
    sSQL = sSQL & "WHERE d.PROJ_TYPE(+) = x.dsti_proj_type and t.prprojectid=p.id AND a.prtaskid = t.prid AND c.prid = t.prchargecodeid AND x.DSTI_PDS_PROJMANAGER = " & rn.Value & " AND x.id = nbi.project_id AND x.id = p.id AND p.is_active = 1 AND p.is_template = 0 AND c.prexternalid NOT IN ('LDEV5150', 'LDEV5200', 'LDEV5205', 'LDEV5215', 'LDEV5100','LDEV5320', 'LDEV5340') AND ETC_HOURS > 0"
    sSQL = sSQL & "Group BY x.dsti_proj_ref,p.name,x.dsti_true_client3,x.dsti_prod_grp,x.dsti_product,d.proj_type_name,x.dsti_unapproved_cat,x.dsti_version,x.dsti_proj_likelihood,x.dsti_rad_indicator"
    sSQL = sSQL & "UNION SELECT 'Documentation',x.dsti_proj_ref ,p.name ,'' ,'' ,'' ,'','' ,x.dsti_version ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,sum(a.practsum/27000) ,Sum (a.prestsum / 27000)"
    sSQL = sSQL & "FROM niku.odf_ca_project x,niku.nbi_project_current_facts nbi,niku.srm_projects p,niku.dsti_project_analysis d,niku.prtask t,niku.prassignment a,niku.prchargecode c"
    sSQL = sSQL & "WHERE d.PROJ_TYPE(+) = x.dsti_proj_type and t.prprojectid=p.id AND a.prtaskid = t.prid AND c.prid = t.prchargecodeid AND x.DSTI_PDS_PROJMANAGER = " & rn.Value & " AND x.id = nbi.project_id AND x.id = p.id AND p.is_active = 1 AND p.is_template = 0 AND c.prexternalid IN ('LDEV5100') AND ETC_HOURS > 0"
    sSQL = sSQL & "Group BY x.dsti_proj_ref,p.name,x.dsti_version"
    sSQL = sSQL & "UNION SELECT 'Regresstion Testing',x.dsti_proj_ref ,p.name ,'' ,'' ,'' ,'','' ,x.dsti_version ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,sum(a.practsum/27000) ,Sum (a.prestsum / 27000)"
    sSQL = sSQL & "FROM niku.odf_ca_project x,niku.nbi_project_current_facts nbi,niku.srm_projects p,niku.dsti_project_analysis d,niku.prtask t,niku.prassignment a,niku.prchargecode c"
    sSQL = sSQL & "WHERE d.PROJ_TYPE(+) = x.dsti_proj_type and t.prprojectid=p.id AND a.prtaskid = t.prid AND c.prid = t.prchargecodeid AND x.DSTI_PDS_PROJMANAGER = " & rn.Value & " AND x.id = nbi.project_id AND x.id = p.id AND p.is_active = 1 AND p.is_template = 0 AND c.prexternalid IN ('LDEV5150', 'LDEV5200', 'LDEV5205', 'LDEV5215', 'LDEV5330') AND ETC_HOURS > 0"
    sSQL = sSQL & "Group BY x.dsti_proj_ref, p.name,x.dsti_version"
    sSQL = sSQL & "UNION SELECT 'Technical Architecture',x.dsti_proj_ref ,p.name ,'' ,'' ,'' ,'','' ,x.dsti_version ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,sum(a.practsum/27000) ,Sum (a.prestsum / 27000)"
    sSQL = sSQL & "FROM niku.odf_ca_project x,niku.nbi_project_current_facts nbi,niku.srm_projects p,niku.dsti_project_analysis d,niku.prtask t,niku.prassignment a,niku.prchargecode c"
    sSQL = sSQL & "WHERE d.PROJ_TYPE(+) = x.dsti_proj_type and t.prprojectid=p.id AND a.prtaskid = t.prid AND c.prid = t.prchargecodeid AND x.DSTI_PDS_PROJMANAGER = " & rn.Value & " AND x.id = nbi.project_id AND x.id = p.id AND p.is_active = 1 AND p.is_template = 0 AND c.prexternalid IN ('LDEV5320') AND ETC_HOURS > 0"
    sSQL = sSQL & "Group BY x.dsti_proj_ref,p.name,x.dsti_version"
    sSQL = sSQL & "UNION SELECT 'Solutions Management',x.dsti_proj_ref ,p.name ,'' ,'' ,'' ,'','' ,x.dsti_version ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,sum(a.practsum/27000) ,Sum (a.prestsum / 27000)"
    sSQL = sSQL & "FROM niku.odf_ca_project x,niku.nbi_project_current_facts nbi,niku.srm_projects p,niku.dsti_project_analysis d,niku.prtask t,niku.prassignment a,niku.prchargecode c"
    sSQL = sSQL & "WHERE d.PROJ_TYPE(+) = x.dsti_proj_type and t.prprojectid=p.id AND a.prtaskid = t.prid AND c.prid = t.prchargecodeid AND x.DSTI_PDS_PROJMANAGER = " & rn.Value & " AND x.id = nbi.project_id AND x.id = p.id AND p.is_active = 1 AND p.is_template = 0 AND c.prexternalid IN ('LDEV5340') AND ETC_HOURS > 0"
    sSQL = sSQL & "Group BY x.dsti_proj_ref,p.name,x.dsti_version"

    With Sheets("Complete").QueryTables(1)
            .Destination = Sheets("Complete").Cells(1, 1)
            .Connection = sConn
            .CommandText = sSQL
            .Refresh BackgroundQuery:=False
            End With

and I get a syntax error on the Rfresh BackgroundQuery line...

 

[xlbo]

BackGroundQuery sets whether code waits until the query is complete before continuing on or whether it just runs straight on - set to false to pause code execution until data has been returned

Syntax error will be to do with the SQL statement I would've thought - what was the exact error ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[t16turbo]

the exact error was just Syntax Error! didn't say enything else.
I guessed at the SQL (eventually) and started commenting out the ststement until I was left with the first select.
Ran it, and fine.
then uncommented the second select (UNION) did the same thing and it worked.

did this until I had the whole code again and it worked!

Excel seems quite random!
its working now!
thanks for your time