Here is some VBA code to get you started. It will run in an Excel spreadsheet.
It opens another spreadsheet, copies the active cell data, pastes that to
Paint, and saves the file as a jpg. I wrote it "on the fly", but it works.
For production use, I would change it so that one sheet in the controlling
spreadsheet contains the names of the other spreadsheets to open. I would add
one worksheet to contain parameters such as the directory locations, and then
call the parameters in. I never hard code parameters into the code except for
quick samples. If the columns are expected to go beyond "Z", then you will
need to add code to check for that.
Tim
Sub OpenAndSave()
processDirectory = "C:\Temp\"
processWorkbook = "myworkbook.xls"
Application.Workbooks.Open (processDirectory & processWorkbook)
LastRow =
LTrim(Str(Workbooks(processWorkbook).Sheets(1).UsedRange.Rows.Count))
LastColumn = Workbooks(processWorkbook).Sheets(1).UsedRange.Columns.Count
LastComumnString = Chr(LastColumn + 64) 'Change the ASCII number into a
character
LastCell = LastComumnString & LastRow
Workbooks(processWorkbook).Sheets(1).Range("A1:" & LastCell).Select
Selection.Copy
ExcelToJPG
Workbooks(processWorkbook).Close
End Sub
Sub ExcelToJPG()
StorageDirectory = "C:\Temp\"
FileSaveName = StorageDirectory & Replace(ActiveWorkbook.Name, ".xls", "")
& ".jpg"
objPaint = "C:\WINDOWS\system32\mspaint.exe"
TaskID = Shell(objPaint, 1)
sngCount = 2
sngStart = Timer
While sngEnd < sngStart + sngCount
sngEnd = Timer
Wend
AppActivate TaskID
Application.SendKeys "^{v}", True
sngCount = 2
sngStart = Timer
While sngEnd < sngStart + sngCount
sngEnd = Timer
Wend
AppActivate TaskID
Application.SendKeys "^{s}", True
sngCount = 2
sngStart = Timer
While sngEnd < sngStart + sngCount
sngEnd = Timer
Wend
Application.SendKeys FileSaveName, True
Application.SendKeys "%{s}", True
sngCount = 2
sngStart = Timer
While sngEnd < sngStart + sngCount
sngEnd = Timer
Wend
Application.SendKeys "%{F4}", True
End Sub
-----Original Message-----
From: Rob Coops [mailto:rco...@gmail.com]
Sent: Monday, June 20, 2011 4:32 PM
To: andrewmchor...@cox.net
Cc: beginners@perl.org
Subject: Re: Convert spreadsheet to jpeg
On Mon, Jun 20, 2011 at 9:57 PM, <andrewmchor...@cox.net> wrote:
> Hello
>
> I am not an expert in perl and so before I propose that a particular script
> be written I would likek to get an idea as to how hard the task would be.
>
> We have a large number (in my opinion) of excel spreadsheets that need to
> be concerted to jpeg format. The jpeg files are used when a document is
> created and the jpeg files are inserted. The spreadsheets are under
> configuration control and the current policy is that when a spreadsheet is
> updated the jpeg file is created (via a macro) and it too goes under CM
> control. I am considering proposing that a perl script could go through the
> directory structure and save each spreadsheet to a jpeg file and it would be
> more efficient and cost effective.
>
> Question:
>
> 1. How hard is it to write a perl script that will open a spreadsheet and
> save it to a jpeg file?
> 2,. Does this capability exist now?
>
> Thanks,
> Andrew
>
> --
> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
> For additional commands, e-mail: beginners-h...@perl.org
> http://learn.perl.org/
>
>
>
Hi Andrew,
Opening a spreadsheet with perl is certainly a simple enough thing to do,
converting to a JPEG is a different story as far as I know as this would
require your perl code to be able to draw the excel documents content. As
far as I am aware this is not possible but I might be wrong. Now you might
be tempted to think that you could just execute the macro but that again
would require perl to fully understand the way MS visual basic works and has
access to all functions and methods that this visual basic does.
I think to be honest that in this case it is probably easier to create a
simple macro to open a specific directory or even a directory provided
trough a popup box, then open each excel file run a fixed macro in there,
safe the file and close the excel, rinse, repeat and you should have the
same thing as your perl script is supposed to do. As you are by the sound of
it creating another MS based document or maybe a PDF file which can be
created out of a MS based document which is then printed to a PDF... again
this can relatively easily be done with an VBA macro.
I know not very perly of me but I am one that believes in using a spade to
dig a hole and drill to drill one ;-)
Regards,
Rob
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
