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:[email protected]]
Sent: Monday, June 20, 2011 4:32 PM
To: [email protected]
Cc: [email protected]
Subject: Re: Convert spreadsheet to jpeg
On Mon, Jun 20, 2011 at 9:57 PM, <[email protected]> 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: [email protected]
> For additional commands, e-mail: [email protected]
> 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: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
