Insert data in worksheet based on criteria? 1

[cutestuff]

hi there,

I need some help. Please.
I have a command button on my excel spreadsheet called "Add data" that basically just adds data from another spreadsheet onto the next available row.
Here is the code:

Dim cnt As ADODB.Connection
Dim stCon As String, stSQL As String, stSQL2 As String
Dim vaNames As Variant
Dim i As Long
Dim xlCalc As Long

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

vaNames = VBA.Array("RFI_Form.xls")

Set cnt = New ADODB.Connection

For i = 0 To UBound(vaNames)
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\" & vaNames(i) & ";" & _
"Extended Properties='Excel 8.0;HDR=Yes'"

stSQL = "INSERT INTO [RFILog$] In 'C:\SEP_RFI_Log.xls' 'Excel 8.0;'" & _
"SELECT * FROM [RFIInfo$A1:BI2]"

With cnt
.Open (stCon) 'Open the connection
.Execute (stSQL) 'Execute the SQL-query.
.Close 'Close the connection
End With

stCon = Empty
stSQL = Empty

Next i

Set cnt = Nothing

With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With

I need to modify it somehow so that it looks through a particular column field (called RFI No) and if the RFI no matches a number already in the spreadsheet, it adds the data to the same row as the RFI No (and not a new row).

Any ideas?

Thanks so much...

 

[SkipVought]

Hi,

BTW, this post belongs in VBA Visual Basic for Applications (Microsoft) forum707.

So you want something like this???

dim rng as range, rst as ADODB.RECORDSET

set rst = new adodb.recordset

rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText

set rng = cells.find("RFI No")
if not rng is nothing then
  'this is the column
   set rng = rng.entirecolumn.find(rst(0))
   if not rng is nothing then
      [b][red]'rng is the cell containing the value[/red][/b]
   end if
end if

Skip,

_[red][/red]

 

[cutestuff]

hi skip!

Thanks! I will try it. Hopefully it works for me.

I've always done Access so I'm pretty new with Excel VBA.

thanks again!
nica

 

[SkipVought]

Why would your SQL be an INSERT query?

I thought you were returning a value to lookup in the column on the other sheet?????????

Skip,

_[red][/red]

 

[cutestuff]

hi skip,

No.
I am basically inserting a row of data from a closed worksheet into my master spreadsheet. It beats having to do data entry twice.

I am actually still trying to work out the query.
I think I need an update query but I can't seem to get my syntax right. .
I need to modify my sql code so that if it finds the same RFI No then it will insert/overwrite the row of data in the master.

Any ideas?

 

[SkipVought]

INSERT works the other way -- you take data from your worksheet and INSERT it into sheet/workbook/database.

You are not doing an UPDATEor INSERT query.

It is very simply a SELECT query that you are returning to this workbook.

Here's what you ought to do, and it will not require any VBA.

On a new sheet in Excel...

Data/Get External Data/New Database Query.

At this point you select the kind of database the source data is in.

If its an Excel Workbook, you select Excel files and then select the workbook and the SHEET or Named Range listed in the presented list.

Once this QueryTable has been inserted in this sheet, all you need to to is Data/Refresh to return new query data.

Will that work?


Skip,

_[red][/red]

 

[cutestuff]

hi Skip,

I know what you're saying about the Excel query. I used that too but I need to put a criteria that searches for a particular column value.

All these would be easier if I could do it in Access but we're sending it out to other people who do not have Access on their computers.

So what we have is a form in Excel. What they do is enter the information in the form (question/answer type).
When the question is entered in the form it goes to me and I open up my master spreadsheet, click "get data" and the row is added to my master.
Now I send the form back out and someone else answers it and the form goes back to me.
Now what I want it to do is when I click "Get data", it adds the answer on the same row that the question is in.

So basically it's like adding two parts (question and answer) to one row. And I have 2 different excel files (master and form) Does that sound confusing?

 

[SkipVought]

OK then back to ADO

A parameter is supplied in Z1

The resultset is appended in the highlighted statement below...

Sub AppendData()
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sh As Range, lRow As Long
    
    sPath = ActiveWorkbook.Path
    sDB = "Backup July 2006 (Week 5)"
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    For Each sh In [SheetName]
        
        sSQL = "SELECT A.PN"
        sSQL = sSQL & ", A.RQDATE"
        sSQL = sSQL & ", A.QTY"
        sSQL = sSQL & ", A.COST"
        sSQL = sSQL & ", A.NOMEN"
        sSQL = sSQL & ", A.`GROUP`"
        sSQL = sSQL & ", A.`Late Pieces`"
        sSQL = sSQL & ", A.BackLog "
        
        sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`" & sh.Value & "$` A "
        
        sSQL = sSQL & "WHERE (A.`Late Pieces`>0 OR A.BackLog>0) "
        sSQL = sSQL & "  AND (A.COE='" & wsData.[Z1] & "') "
        
        [Sql] = sSQL
        
        With rst
           .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
           
            With wsData
               lRow = .UsedRange.Rows.Count + 1[b]
               .Cells(lRow, 1).CopyFromRecordset rst[/b]
               .Range(.Cells(lRow, .UsedRange.Columns.Count), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Value = sh.Value
            End With
        
           .Close
        End With
    Next
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Skip,

_[red][/red]