Retrive data and paste using Excel VBA -
vba code here
'this function ur comnad button private sub cmdgetdata() call getdata end sub here module 1 getting data configuration file.
'module 1 public sub getdata() dim oexcelconn adodb.connection '===== dim oconn adodb.connection '* connection string dim orst adodb.recordset '* record set dim squery string '* query string dim sconnstr string dim wb workbook, wbcurr workbook sconnstr = getexcelconnstr(thisworkbook.path & "\data\exceldata.xlsx") set oconn = new adodb.connection oconn.open sconnstr set wb = workbooks.open thisworkbook.path & "\template\mytemplate.xltx" ictr = 1 sheet2.range("name_std").rows.count squery = "select * [config$a:r] column = " & sheet2.range("name_std").cells(ictr,1) set orst = new adodb.recordset orst.open squery, oconn set wbcurr = wb.worksheets("sheet1").copy after:=worksheets("sheet1") wbcurr.range("a2").cpoyfromrecordset orst next end sub here module 1 getting data configuration file. here module 1 getting data configuration file. here module 1 getting data configuration file. here module 1 getting data configuration file. here module 1 getting data configuration file. here module 1 getting data configuration file.
public function getexcelconnstr(spath string) string dim sconnstr string sconnstr = "provider=microsoft.ace.oledb.12.0;data source=" & spath & ";extended properties=""excel 12.0 xml;hdr=yes"";" getexcelconnstr = sconnstr end function
set wks = activeworkbook.worksheets("template") ' wks.range("b2").cells(1, 17).value dim integer dim startindex string dim endindex string dim wsname string wsname = "temp" dim str string = 1 17 str = wks.range("b2").cells(i, 1).value if str = "grand total" endindex = wks.range("b2").cells(i, 1).address exit end if next startindex = wks.range("b2").cells(1, 1).address endindex = wks.range("b2").cells(i - 1, 17).address wks.range(startindex & ":" & endindex).copy thisworkbook.worksheets(wsname).select activesheet.range("b2").select activesheet.paste
Comments
Post a Comment