Excel query showing up with blanks?

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
Hi All,

I hope you can assist as need this working for tomorrow - The code below gets data from Worksheet $DataSheet however it shows up blank rows from the database and I can't seem to see why its doing this? :/

VBA Code:
Sub sbADO()
Dim sSQLQry As String
Dim sSQLSting As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String

DBPath = ThisWorkbook.FullName

sconnect = "Provider=jken.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect
 sSQLSting = "Select [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8, [Column9], [Column10] from [DataSheet$] WHERE [Column1] in ('" & _
Join(Application.Transpose(Range("A2:A50").Value), "','") & "')"
 
    mrs.Open sSQLSting, Conn
        ActiveSheet.Range("A2").CopyFromRecordset mrs
    mrs.Close
Conn.Close

    Range("K2").Formula = "=IF($H2="""","""",$H2)"                                              
    Range("l2").Formula = "=TEXT(IF($G2<=$K2,$G2,$K2),""dd mmmm yyyy"")"                        
    Range("M2").Formula = "=TEXT(IF($I2="""",""Currently Working"",$I2),""dd mmmm yyyy"")"      
    Range("N2").Formula = "=CONCATENATE($D2,"" "",$E2)"                                          
    Range("P2").Formula = "=TEXT(CONCATENATE(""Document"", "" For "", $D2, "" "", $E2),)"   
    
    Range("K2:K50").FillDown
    Range("l2:L50").FillDown
    Range("M2:M50").FillDown
    Range("N2:N50").FillDown
    Range("P2:P50").FillDown
    
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Mike2502

Board Regular
Joined
Jan 19, 2020
Messages
139
Office Version
  1. 2010
UPDATE -

Finally figured out what it is; On my datasheet I had a few rows where 'COLUMN1' was blank and as on ActiveSheet A2:A50 was not completely filled it copy and pasted them

Is there anyway to check whether cell contains values then run SQL up to that row?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,995
Members
415,873
Latest member
fuulhouse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top