Can I save a copy of Excel workbook in MSSQL database using VBA?

K

Karla

Our User group would like a button in an Excel workbook that saves a copy of
the workbook in MSSQL database. We have done something similiar in a Web app
using C#. The excel workbook is stored in MSSQL db as an image. That app has
access to the workbook as a saved file, not as an active workbook during that
process. Converts the file to byte then image.

I can't duplicate the process successfully in VBA code. The workbook object
doesn't seem to support the Savepicture or CopyAsPicture functions. In order
to use the byte conversion I need the Length of the file(workbook), another
property I am unable to use with the workbook object.

Any help would be appreciated. I am using VBA 6.3. Thanks.
 
N

NickHK

Karla,
You cannot convert a WB to an image, but you can Copy/Paste Special a range
as a picture.
However, on my systems, you are limited to relatively small ranges.
You would still have to get the picture out of Excel and into a suitable
graphics format, either in memory or on disk, before you can send the bytes
to the DB.

If you could do it in C++, something similar in VB/VBA will (probably)
exist. Depends how you did it ?

It would be easier to install a <printer> that can convert to a graphic.
Possibly:
http://www.print-driver.com/howto/converting/convert_microsoft_excel_table_to_jpeg.htm

NickHK
 
K

Karla

Nick,

Thank you for your input. The 3rd party tool to convert to a graphic is
probably not an option as we would have to purchase licenses for an
enterprise application.

I will continue to pursue a VBA solution.
In C# (webapp) the code was:

// Get a ref to a PostedFile object
HttpPostedFile postedFile = myFile.PostedFile;

//Get size of uploaded file
int nFileLen = postedFile.ContentLength;

string fileNameFullPath = postedFile.FileName;
string filename = Path.GetFileName(fileNameFullPath);
//Allocate an byte array to store the data from the file uploaded
byte[] myData = new byte[nFileLen];
// setup SQL access (connection info omitted)
addEmp.Parameters.Add("@excel_workbook", SqlDbType.Image,
myData.Length).Value = myData;


Karla
 
N

NickHK

Karla,
If it is licensing issue on that component that you use already, then it is
irrelevant.
Install a virtual printer that output in the desired image format, or one
the many freely available PDF printers.

NickHK

Karla said:
Nick,

Thank you for your input. The 3rd party tool to convert to a graphic is
probably not an option as we would have to purchase licenses for an
enterprise application.

I will continue to pursue a VBA solution.
In C# (webapp) the code was:

// Get a ref to a PostedFile object
HttpPostedFile postedFile = myFile.PostedFile;

//Get size of uploaded file
int nFileLen = postedFile.ContentLength;

string fileNameFullPath = postedFile.FileName;
string filename = Path.GetFileName(fileNameFullPath);
//Allocate an byte array to store the data from the file uploaded
byte[] myData = new byte[nFileLen];
// setup SQL access (connection info omitted)
addEmp.Parameters.Add("@excel_workbook", SqlDbType.Image,
myData.Length).Value = myData;


Karla


NickHK said:
Karla,
You cannot convert a WB to an image, but you can Copy/Paste Special a range
as a picture.
However, on my systems, you are limited to relatively small ranges.
You would still have to get the picture out of Excel and into a suitable
graphics format, either in memory or on disk, before you can send the bytes
to the DB.

If you could do it in C++, something similar in VB/VBA will (probably)
exist. Depends how you did it ?

It would be easier to install a <printer> that can convert to a graphic.
Possibly:
http://www.print-driver.com/howto/converting/convert_microsoft_excel_table_to_jpeg.htm

NickHK

copy
of Web
app app
has during
that In
order
 
K

Karla

Nick,
I am not certain that a virtual printer conversion is a viable solution as I
would have to deploy and install it for all the users of the Excel workbook.
The Excel workbook runs on their local machines not a server type setup.

Karla

NickHK said:
Karla,
If it is licensing issue on that component that you use already, then it is
irrelevant.
Install a virtual printer that output in the desired image format, or one
the many freely available PDF printers.

NickHK

Karla said:
Nick,

Thank you for your input. The 3rd party tool to convert to a graphic is
probably not an option as we would have to purchase licenses for an
enterprise application.

I will continue to pursue a VBA solution.
In C# (webapp) the code was:

// Get a ref to a PostedFile object
HttpPostedFile postedFile = myFile.PostedFile;

//Get size of uploaded file
int nFileLen = postedFile.ContentLength;

string fileNameFullPath = postedFile.FileName;
string filename = Path.GetFileName(fileNameFullPath);
//Allocate an byte array to store the data from the file uploaded
byte[] myData = new byte[nFileLen];
// setup SQL access (connection info omitted)
addEmp.Parameters.Add("@excel_workbook", SqlDbType.Image,
myData.Length).Value = myData;


Karla


NickHK said:
Karla,
You cannot convert a WB to an image, but you can Copy/Paste Special a range
as a picture.
However, on my systems, you are limited to relatively small ranges.
You would still have to get the picture out of Excel and into a suitable
graphics format, either in memory or on disk, before you can send the bytes
to the DB.

If you could do it in C++, something similar in VB/VBA will (probably)
exist. Depends how you did it ?

It would be easier to install a <printer> that can convert to a graphic.
Possibly:
http://www.print-driver.com/howto/converting/convert_microsoft_excel_table_to_jpeg.htm

NickHK

Our User group would like a button in an Excel workbook that saves a copy
of
the workbook in MSSQL database. We have done something similiar in a Web
app
using C#. The excel workbook is stored in MSSQL db as an image. That app
has
access to the workbook as a saved file, not as an active workbook during
that
process. Converts the file to byte then image.

I can't duplicate the process successfully in VBA code. The workbook
object
doesn't seem to support the Savepicture or CopyAsPicture functions. In
order
to use the byte conversion I need the Length of the file(workbook),
another
property I am unable to use with the workbook object.

Any help would be appreciated. I am using VBA 6.3. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top