M
Michelle
Hi,
I am trying to write detail records from one spreadsheet to the other. B2 is
used to select records(marked with an "x") I want to transfer a part
number(C2) if B2 = "x". So if B2 = "x" transfer C2.
Can I build an array for this? Also, I'd like to add some hard-coded fields
to that spreadsheet as well. For example, UOM = "EA", Action = "Add", A count
of detail lines added that's formatted as 0010,0020,0030... Here is the code
I'm trying to use. Thank You for your help.
At this point I'm just trying to write 1 column to the other spreadsheet.
Set rnData = ActiveSheet.Range("B11:C150")
vaData = Application.Transpose(rnData.Value)
Set cnt = New ADODB.Connection
Set rst = New ADODB.recordset
Set wbSource = ThisWorkbook
Set wsSheet = wbSource.Worksheets("Deliverables")
stWbtarget = (strPath & strFileName)
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stWbtarget & ";" & _
"Extended Properties='Excel 8.0;HDR=No';"
'"Data Source=" & CurrentProject.Path & "\" & strExcelName & ";" _
vaData = rnData.Value
cnt.Open stConn
'SQL statement for BLS
stSQL = "SELECT * FROM [Sheet1$D10300]"
With rst
.CursorLocation = adUseClient
.Open stSQL, cnt, adOpenForwardOnly, adLockOptimistic, adCmdText
End With
For i = 1 To UBound(vaData)
With rst
.AddNew
If (vaData(i, 1)) = "x" Then
.Update VBA.Array(vaData(i, 2))
End If
End With
Next i
I am trying to write detail records from one spreadsheet to the other. B2 is
used to select records(marked with an "x") I want to transfer a part
number(C2) if B2 = "x". So if B2 = "x" transfer C2.
Can I build an array for this? Also, I'd like to add some hard-coded fields
to that spreadsheet as well. For example, UOM = "EA", Action = "Add", A count
of detail lines added that's formatted as 0010,0020,0030... Here is the code
I'm trying to use. Thank You for your help.
At this point I'm just trying to write 1 column to the other spreadsheet.
Set rnData = ActiveSheet.Range("B11:C150")
vaData = Application.Transpose(rnData.Value)
Set cnt = New ADODB.Connection
Set rst = New ADODB.recordset
Set wbSource = ThisWorkbook
Set wsSheet = wbSource.Worksheets("Deliverables")
stWbtarget = (strPath & strFileName)
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stWbtarget & ";" & _
"Extended Properties='Excel 8.0;HDR=No';"
'"Data Source=" & CurrentProject.Path & "\" & strExcelName & ";" _
vaData = rnData.Value
cnt.Open stConn
'SQL statement for BLS
stSQL = "SELECT * FROM [Sheet1$D10300]"
With rst
.CursorLocation = adUseClient
.Open stSQL, cnt, adOpenForwardOnly, adLockOptimistic, adCmdText
End With
For i = 1 To UBound(vaData)
With rst
.AddNew
If (vaData(i, 1)) = "x" Then
.Update VBA.Array(vaData(i, 2))
End If
End With
Next i