Exporting Excel chart into file using VB
Hello,
I'm somewhat of a novice to VB, and I have a question...I am trying to export a formula-driven Excel chart into another Excel document with no formulas (i.e. values only). Furthermore, I would like to be able to automatically generate the filename AND sheet name of the exported file through the original Excel file.
Any advice would be appreciated.
Thanks,
Mark
Re: Exporting Excel application to another Excel file
Quote:
Originally Posted by yankeeyosh26
Hello,
I'm somewhat of a novice to VB, and I have a question...I am trying to export a formula-driven Excel chart into another Excel document with no formulas (i.e. values only). Furthermore, I would like to be able to automatically generate the filename AND sheet name of the exported file through the original Excel file.
Any advice would be appreciated.
Thanks,
Mark
This will help you in exporting a formula-driven Excel chart as gif image and then you can call it back to the new document...
http://www.vbforums.com/showpost.php...68&postcount=5
generating a new filename and sheetname shouldn't be difficult. it all depends on how do you want to rename it... Do you want to use the dates or specific format etc etc...
Hope this helps...
Re: Exporting Excel application to another Excel file
Quote:
Originally Posted by koolsid
This will help you in exporting a formula-driven Excel chart as gif image and then you can call it back to the new document...
http://www.vbforums.com/showpost.php...68&postcount=5
generating a new filename and sheetname shouldn't be difficult. it all depends on how do you want to rename it... Do you want to use the dates or specific format etc etc...
Hope this helps...
I tried this, and I got an error on the line where mychart is set (Run-time error '1004': Application-defined or object-defined error). Any idea what is going on?
I want to rename the files based on two input parameters that can be entered on the main spreadsheet (one for the filename, one for the sheetname).
Thanks.
Re: Exporting Excel application to another Excel file
Did you replace "Chart 1" with the name of your chart?
Re: Exporting Excel application to another Excel file
Quote:
Originally Posted by koolsid
Did you replace "Chart 1" with the name of your chart?
I think so. I tried it in two different ways (in the project window, the sheet I am trying to export is called "S (chart)", where "chart" is what I called the chart...no idea why "S" is called "S" and not "Sheet1". But now the error I am getting is "Unable to get the ChartObjects property of the Worksheet class".
Re: Exporting Excel application to another Excel file
ok
here it is...
Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects window, and choose Insert, Module to insert a new VBA module. Then type the following four-line procedure into the module:
Code:
Sub exportmychart()
'Replace the path below with your path
Fname = "S:\New Folder\" & ActiveChart.Name & ".gif"
ActiveChart.Export FileName:=Fname, FilterName:="GIF"
End Sub
After the macro is entered, reactivate Excel and click the chart to be saved. Press Alt-F8 to display the Macro dialog box. Select the exportmychart macro and click Run. your chart will be save in the location you specified...
Hope this helps :)
Re: Exporting Excel application to another Excel file
Quote:
Originally Posted by koolsid
ok
here it is...
Press Alt-F11 to activate the Visual Basic editor. Select your workbook in the Projects window, and choose Insert, Module to insert a new VBA module. Then type the following four-line procedure into the module:
Code:
Sub exportmychart()
'Replace the path below with your path
Fname = "S:\New Folder\" & ActiveChart.Name & ".gif"
ActiveChart.Export FileName:=Fname, FilterName:="GIF"
End Sub
After the macro is entered, reactivate Excel and click the chart to be saved. Press Alt-F8 to display the Macro dialog box. Select the exportmychart macro and click Run. your chart will be save in the location you specified...
Hope this helps :)
When I click F11, I get a new tab that says Chart1. Just FYI, this is just a table on a normal Excel sheet; not a chart. Sorry if there is any confusion.
Anyway, when I tried running this, I got Run-time error '91': Object variable or With block variable not set.
Re: Exporting Excel application to another Excel file
[QUOTE=yankeeyosh26]When I click F11, I get a new tab that says Chart1. QUOTE]
1) You need to click Alt plus F11 and not F11 to access the VBA Editor
Quote:
Just FYI, this is just a table on a normal Excel sheet; not a chart. Sorry if there is any confusion.
:eek:
What do you mean????
Re: Exporting Excel application to another Excel file
[QUOTE=koolsid]
Quote:
Originally Posted by yankeeyosh26
When I click F11, I get a new tab that says Chart1. QUOTE]
1) You need to click Alt plus F11 and not F11 to access the VBA Editor
:eek:
What do you mean????
Whoops...never mind...I misread, and only pressed F11. I am still getting that runtime error. Under Fname, I assume that's where the file is to be sent, right? I'm just putting it in my C drive, but for some reason, it is giving me that error.
Also, from what I have read, ActiveChart is used for embedded charts...I really apologize for the nomenclature; there are no "charts" anywhere in this application. It is all just formulas. I simply want to export the results into another spreadsheet (without formulas).
Re: Exporting Excel application to another Excel file
At the end what you want is to copy a range of cells then paste as values to another workbook.
Re: Exporting Excel application to another Excel file
@yankeeyosh26, Please dont post duplicate threads. Dup thread deleted.
Thanks Anhn.