Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Macro Running Sql Query

  1. #1
    Join Date
    28th March 2007
    Posts
    5

    Macro Running Sql Query

    I want to write some VBA that retrieves data from a lotus notes database into excel. I will then have some other code that manipulates the data for the user. I have managed to do this with MS Query manually. However when I start to put into VBA my problems start.

    As I do not know Sql I use the macro record function. This worksso far so good. The problems seem to start when I re-run the macro and try to save the workbook. Excel just sits there with the CPU running at 99%. I left the pc for an hour and it was still in the same condition with the status bar showing save.

    Any ideas as it is very frustrating.

    I have pasted the macro recorded below:
    VB:
    Sub Macro4() 
         '
         ' Macro4 Macro
         ' Macro recorded 02/04/2007 by cjohn
         '
         
         '
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ 
            "ODBC;DSN=premax;Database=Premax\se000273.nsf;Server=local;UserName=John Cross/bsp;EncryptMaxSubquery=20;MaxStmtLen=4096;MaxRels=20;M" _ 
            ), Array( _ 
            "axVarcharLen=1024;KeepTempIdx=1;MaxLongVarcharLen=1024;ShowImplicitFlds=0;MapSpecialChars=1;ThreadTimeout=60;" _ 
            )), Destination:=Range("E1")) 
            .CommandText = Array( _ 
            "SELECT ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".DocIDNo, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Action, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Action_AddI" _ 
            , _ 
            "nfo_ViewDspl, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Cause, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".CreatingDate, ""7___Special___c___Import_Export___e___Fault___Event___Action_1""." _ 
            , _ 
            "CustDocRef, ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".Customer  FROM ""7___Special___c___Import_Export___e___Fault___Event___Action"" ""7___Special___c___Import_Export___e___Fault___Event___Action_1""  WHERE (""7" _ 
            , _ 
            "___Special___c___Import_Export___e___Fault___Event___Action_1"".CreatingDate Between '2007-02-04 14:47:01' And '2007-03-05 07:14:37')  ORDER BY ""7___Special___c___Import_Export___e___Fault___Event___Action_1"".DocIDNo" _ 
    ) 
            .Name = "Query from premax_range" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .BackgroundQuery = True 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .PreserveColumnInfo = True 
            .SourceConnectionFile = _ 
            "C:\Documents and Settings\cjohn\Desktop\Faults development\Query from premax_range.dqy" 
            .refresh BackgroundQuery:=False 
        End With 
    End Sub 
    
    
    Last edited by ByTheCringe2; April 2nd, 2007 at 16:50.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th March 2007
    Posts
    5

    Re: Macro Running Sql Query

    I have done some more testing. It does not seem to be the macro but rather if I save the file with the query parameters; save query definition selected.

    I do not know why excel can not cope with this. I am retrieving 1700 records but surely this is not a lot.

    Does any one know of a way round this like to be able to turn off the "save query definition" before save.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th March 2007
    Posts
    5

    Re: Macro Running Sql Query

    I have found a way of getting the save to work by deleting the query but there must be a better way of doing this as to refresh the data I will have to reload the query. If anyone has any better ideas please let me know.

    VB:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
        Dim QT_count As Integer 
         
         
        Sheet2.Select 
        QT_count = ActiveSheet.QueryTables.count 
        If QT_count > 0 Then 
            ActiveSheet.QueryTables(QT_count).Delete 
             
        End If 
         
         
         
    End Sub 
    
    
    Last edited by ByTheCringe2; April 2nd, 2007 at 16:51.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Macro Running Sql Query

    Welcome to the forum. However please read the rules and use code tags for VBA. I have added them for you this time, but normally the posts would be deleted.

    Moving this to the SQL forum.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,376

    Re: Macro Running Sql Query

    Click Here For More Info

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Running Report on Crosstab Query when Data is not in Defined Fields
    By Aluura in forum Excel and/or Access Help
    Replies: 2
    Last Post: June 29th, 2007, 01:10
  2. Timeout Error When Running Sql Query From Vba
    By brolling in forum Excel and/or SQL Help
    Replies: 6
    Last Post: January 19th, 2007, 07:02
  3. Replies: 9
    Last Post: August 13th, 2006, 11:22
  4. Running a saved query
    By jchoo in forum Excel and/or Access Help
    Replies: 1
    Last Post: November 17th, 2005, 07:53
  5. Running an Access Query on the Web
    By cw_lynn in forum Excel and/or Access Help
    Replies: 1
    Last Post: June 2nd, 2004, 03:30

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno