VBA Macro to extract data from a chart in Excel 2007, 2010, and 2013 -
i sent excel sheet 4 charts. data charts in workbook not provided.
goal: want extract data charts using vba sub.
problem: having trouble "type mismatch." when try assign variant array oseries.xvalues range of cells.
option explicit option base 1 ' 1. enter following macro code in module sheet. ' 2. select chart want extract underlying data values. ' 3. run getchartvalues sub. data chart placed in new worksheet named "chartname data". ' sub getchartvalues() ' dim lxnumberofrows long dim lynumberofrows long dim oseries series dim lcounter long dim oworksheet worksheet dim ochart chart dim xvalues() variant dim yvalues() variant dim xdestination range dim ydestination range set ochart = activechart ' if chart not active, exit if ochart nothing exit sub end if ' create worksheet storing data set oworksheet = activeworkbook.worksheets.add oworksheet.name = ochart.name & " data" ' loop through series in chart , write there values ' worksheet. lcounter = 1 each oseries in ochart.seriescollection xvalues = oseries.xvalues yvalues = oseries.values ' calculate number of rows of data. 1048576 maximum number of rows in excel. lxnumberofrows = worksheetfunction.min(ubound(oseries.xvalues), 1048576 - 1) lynumberofrows = worksheetfunction.min(ubound(oseries.values), 1048576 - 1) ' array big, chop off end redim preserve xvalues(lxnumberofrows) redim preserve yvalues(lynumberofrows) oworksheet ' put name of series @ top of each column .cells(1, 2 * lcounter - 1) = oseries.name .cells(1, 2 * lcounter) = oseries.name set xdestination = .range(.cells(1, 2 * lcounter - 1), .cells(lxnumberofrows + 1, 2 * lcounter - 1)) set ydestination = .range(.cells(1, 2 * lcounter), .cells(lxnumberofrows + 1, 2 * lcounter)) 'assign x , y data chart range in worksheet xdestination.value = application.transpose(xvalues) ydestination.value = application.transpose(yvalues) ' not work either ' .range(.cells(2, 2 * lcounter - 1), .cells(lxnumberofrows + 1, 2 * lcounter - 1)).value = application.transpose(oseries.xvalues) ' .range(.cells(2, 2 * lcounter), .cells(lxnumberofrows + 1, 2 * lcounter)).value = application.transpose(oseries.values) end lcounter = lcounter + 1 next ' cleanup set ochart = nothing set oworksheet = nothing end sub the main issue following lines:
.range(.cells(2, 2 * lcounter - 1), .cells(lxnumberofrows + 1, 2 * lcounter - 1)) = application.transpose(oseries.xvalues) .range(.cells(2, 2 * lcounter), .cells(lxnumberofrows + 1, 2 * lcounter)) = application.transpose(oseries.values) upon further inspection using locals window, find following: 
the below code works while above code not.
sub test2() dim a(6) variant 'a(1) = 1 a(2) = 2# a(3) = 3# a(4) = 4# a(5) = 5# range(cells(1, 1), cells(6, 1)).value = application.transpose(a) end sub why doesn't first piece of code work?
looping on many cells slow in case (i've tried). please, don't use loop unless seconds 1,000,000 element.
the main cause built-in transpose function. transpose can handle arrays 2^16 or less elements.
the code below works well. handles problem of transpose function limitation of 2^16 elements. uses loop loop fast arrays. 4 series , each having 1048576 elements, sub took 10 seconds run. acceptable.
option explicit option base 1 ' 1. enter following macro code in module sheet. ' 2. select chart want extract underlying data values. ' 3. run getchartvalues sub. data chart placed in new worksheet named "chartname data". ' public sub getchartvalues() dim lxnumberofrows long dim lynumberofrows long dim oseries series dim lseriescounter long dim oworksheet worksheet dim ochart chart dim xvalues() variant dim yvalues() variant dim xdestination range dim ydestination range set ochart = activechart ' if chart not active, exit if ochart nothing exit sub end if ' create worksheet storing data set oworksheet = activeworkbook.worksheets.add oworksheet.name = ochart.name & " data" ' loop through series in chart , write values worksheet. lseriescounter = 1 each oseries in ochart.seriescollection ' x , y values xvalues = oseries.xvalues yvalues = oseries.values ' calculate number of rows of data. lxnumberofrows = ubound(xvalues) lynumberofrows = ubound(yvalues) ' 1048576 maximum number of rows in excel. array big. chop off end. if lxnumberofrows >= 1048576 lxnumberofrows = 1048576 - 1 redim preserve xvalues(lxnumberofrows) end if if lynumberofrows >= 1048576 lynumberofrows = 1048576 - 1 redim preserve yvalues(lynumberofrows) end if oworksheet ' put name of series @ top of each column .cells(1, 2 * lseriescounter - 1) = oseries.name & " x values" .cells(1, 2 * lseriescounter) = oseries.name & " y values" set xdestination = .range(.cells(2, 2 * lseriescounter - 1), .cells(lxnumberofrows + 1, 2 * lseriescounter - 1)) set ydestination = .range(.cells(2, 2 * lseriescounter), .cells(lxnumberofrows + 1, 2 * lseriescounter)) end ' arrays larger 2^16 fail transpose function. therefore must manually transpose if lxnumberofrows > 2& ^ 16 'assign x , y data chart range in worksheet. use manualtranspose 2^16 or more elements. xdestination.value = manualtranspose(xvalues) ydestination.value = manualtranspose(yvalues) else 'assign x , y data chart range in worksheet. use built-in transpose less 2^16 elements. xdestination.value = worksheetfunction.transpose(xvalues) ydestination.value = worksheetfunction.transpose(yvalues) end if lseriescounter = lseriescounter + 1 next ' cleanup set ochart = nothing set oworksheet = nothing end sub ' helper function when built-in transpose function cannot used. arrays larger 2^16 must transposed manually. private function manualtranspose(byref arr variant) variant dim arrlength long dim long dim transposedarray() variant arrlength = ubound(arr) redim transposedarray(arrlength, 1) = 1 arrlength transposedarray(i, 1) = arr(i) next manualtranspose = transposedarray end function
Comments
Post a Comment