Code Error

wallar · Mar 15th 2007

I am using the attached code however when my database query does not return any data I dont want the Dim function to perform. can anyone help?

Code

Sub macro3()
     
    Worksheets("Data 1").Range("I3:BU65536").ClearContents
     
     'declare a variable to hold date
    Dim strDate As String
    strDate = Format(CDate(Range("startDate").Value), "yyyy-mm-dd") & " " & FormatDateTime(CDate(Range("startDate").Value), vbLongTime)
     
    Sheets("Data 1").Select
    Range("A1").Select
    With Selection.QueryTable
        .Connection = _
        "ODBC;DSN=ewd;UID=gents;APP=Microsoft® Query;WSID=CUSXP02;DATABASE=EWD;Trusted_Connection=Yes"
        .CommandText = Array( _
        "SELECT EWD_Object.Department, EWD_Object.Doctype, EWD_Object.Gendate, EWD_Object.ArchiveEntryDate, EWD_Object.Docorigin, EWD_Object.AccountNo, EWD_Object.EnquiryNo" & Chr(13) & "" & Chr(10) & "FROM EWD.dbo.EWD_Object EWD_Object" & Chr(13) & "" _
        , _
       "" & Chr(10) & "WHERE (EWD_Object.Department In ('BBI','BBA')) AND (EWD_Object.ArchiveEntryDate={ts " & "'" & strDate & "'" & "}) AND (EWD_Object.Docorigin<>'Folder')" & Chr(13) & "" & Chr(10) & "ORDER BY EWD_Object.Doctype" _
)
        .Refresh BackgroundQuery:=False
    End With
        
    Dim Last_Row As Long
     
    Last_Row = Range("A65535").End(xlUp).Row
    Range("I2:BU2").AutoFill Destination:=Range("I2:BU" & Last_Row), Type:=xlFillDefault
     
End Sub

Display More

Robert B · Mar 15th 2007

Re: Code Error

Hi

not quite sure what you mean by the Dim function not performing. Dim is simply a way of declaring a variable. Do you mean that you don't want the LastRow variable to be set, and that the subsequent code should not run if LastRow is Null or zero?

Robert

wallar · Mar 15th 2007

Re: Code Error

Quote from Robert B

Hi

not quite sure what you mean by the Dim function not performing. Dim is simply a way of declaring a variable. Do you mean that you don't want the LastRow variable to be set, and that the subsequent code should not run if LastRow is Null or zero?

Robert

Yes, sorry new to these codes

wallar · Mar 19th 2007

Re: Code Error

Any ideas?

Robert B · Mar 19th 2007

Re: Code Error

Hi

I assume your query will always return something even if it is Null, so try

Code

Dim Last_Row As Long 
        Last_Row = Range("A65535").End(xlUp).Row 
If LastRow =<1 Then 
Goto 10 
End If
    Range("I2:BU2").AutoFill Destination:=Range("I2:BU" & Last_Row), Type:=xlFillDefault 


10

if this is not the case then try

Code

On Error Goto 10


Sheets("Data 1").Select 
    Range("A1").Select 
    With Selection.QueryTable 
        .Connection = _ 
        "ODBC;DSN=ewd;UID=gents;APP=Microsoft® Query;WSID=CUSXP02;DATABASE=EWD;Trusted_Connection=Yes" 
        .CommandText = Array( _ 
        "SELECT EWD_Object.Department, EWD_Object.Doctype, EWD_Object.Gendate, EWD_Object.ArchiveEntryDate, EWD_Object.Docorigin, EWD_Object.AccountNo, EWD_Object.EnquiryNo" & Chr(13) & "" & Chr(10) & "FROM EWD.dbo.EWD_Object EWD_Object" & Chr(13) & "" _ 
        , _ 
        "" & Chr(10) & "WHERE (EWD_Object.Department In ('BBI','BBA')) AND (EWD_Object.ArchiveEntryDate={ts " & "'" & strDate & "'" & "}) AND (EWD_Object.Docorigin<>'Folder')" & Chr(13) & "" & Chr(10) & "ORDER BY EWD_Object.Doctype" _ 
) 
        .Refresh BackgroundQuery:=False 
    End With 
     
    Dim Last_Row As Long 
     
    Last_Row = Range("A65535").End(xlUp).Row 
    Range("I2:BU2").AutoFill Destination:=Range("I2:BU" & Last_Row), Type:=xlFillDefault 
     
10

Display More

Robert

Code Error

Participate now!

Share