Export data to new WB and Add sheets

[sujittalukde]

I am copying data from website to a excel WB named "Inputdata" for stocks say stock code "SAIL". Data are related to "Quarterly Income statement", "Annual Income Statement", and "Annual Balance sheet". On completion of importing the data I want to save those data in a master WB say "Masterdata" with a pre formatted manner. nad say the masterdata WB will be kept at D:\Results.

When the user will change the stock code at the inputdata.xls at a cell A1 then the data should be transferred to the masterdata.xls with new sheets to be added at the masterdata. Suppose user has changed the stock code from "SAIL" to "TISCO", then the data of TICO shall be stored to the masterdata.xls as a new sheet. and so on.

How this can be achieved?

Attaching the workbooks for ready reference and testing.

[qff]

Hi

here's some code to get you started. Directory paths etc will need to be changed.

You need to add a reference to the Microsoft ActiveX Data Objects 2.7 library.

Sub exporter()
Dim mysheet As String
mysheet = "[" & Range("H1") & "]"

Dim mycon As ADODB.Connection
Set mycon = New ADODB.Connection

With mycon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MasterData.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

Dim mydata As String

mydata = "SELECT * INTO [C:\MasterData.xls]." & mysheet & " FROM [C:\InputData.xls].[Annual Balance Sheet$]"

Dim myrs As ADODB.Recordset
Set myrs = mycon.Execute(mydata)

mycon.Close

Set mycon = Nothing
Set myrs = Nothing

End Sub

hope this helps
regards
qff