Write to a closed workbook

[cgcamal]

Hi everybody,

I hope someone could help to modify the code below to get what I looking for.

I found the macro below here, and is an example code that I think could be addapted to my needs but
I don´t know how to deal with combination of VBA and SQL commands.

The macro updates a closed workbook when I change values in an opened workbook. The code is:

[FONT=Arial][SIZE=2][COLOR=#000000]Sub UpdateWBK()
[COLOR=Green]'[/COLOR][/COLOR][/SIZE][/FONT][COLOR=Green][B][FONT=Arial][SIZE=2]1) Create a new workbook.  In A1 enter H1.  This is the column Heading.  Save the file as c:\temp\a.xls[/SIZE][/FONT][/B][/COLOR]
[COLOR=Green][B][FONT=Arial][SIZE=2]'2) In a new open workbook, enter your input number in A1 (say 15).  Insert a general module, and insert the code[/SIZE][/FONT][/B][/COLOR]
[B][FONT=Arial][SIZE=2][COLOR=#000000][COLOR=Green]'3-) Run the code.[/COLOR]
[COLOR=Green]'4-) Open a.xls and you should find that A2 contains 15.  Shut down a.xls,  and change the value in A1 from 15 to 20.  Rerun the code.  Open a.xls  and you should find that A2 is now 35.[/COLOR][/COLOR][/SIZE][/FONT][/B][COLOR=Green]

[/COLOR][FONT=Arial][SIZE=2][COLOR=#000000]  Set cn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
    
  cn.Open "provider=microsoft.jet.oledb.4.0;data source = c:\temp\a.xls; extended properties = ""excel 8.0; imex=1, hdr=yes"""
  rs.Open "select * from [sheet1$]", cn, 3, 3 'adOpenStatic, adLockOptimistic
  Range("B1").CopyFromRecordset rs
  rs.MoveFirst
  rs.Fields(0).Value = Range("A1").Value + Range("B1").Value
  rs.Update
  
  Set rs = Nothing
  Set cn = Nothing
End Sub[/COLOR][/SIZE][/FONT]

and I would like to modify the macro to get this:
1-) When in open workbook I enter values any sheet1 to sheet50 in range B:I, write these values to next
empty row in closed workbook in range B:I (no the same order).

2-) Value I enter in column B in opened workbook is the trigger that says if I have to add the new values
in sheetX, sheetY or sheetZ in closed workbook.

Any help would be very appreciated.

Regards.

 

[cgcamal]

*bump*

 

[cgcamal]

Somebody knows?

 

[cgcamal]

nobody knows?