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

[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.

 

[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

 

[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

 

[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

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

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

 

[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

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

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

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.