transfer data from one workbook to another.

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$D10:D300]"

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top