Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save a copy of Excel workbook in MSSQL database using VBA?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save a copy of Excel workbook in MSSQL database using VBA?
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/co...le_to_jpeg.htm NickHK "Karla" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save a copy of Excel workbook in MSSQL database using VB
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" wrote: 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/co...le_to_jpeg.htm NickHK "Karla" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save a copy of Excel workbook in MSSQL database using VB
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" wrote in message ... 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" wrote: 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/co...le_to_jpeg.htm NickHK "Karla" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I save a copy of Excel workbook in MSSQL database using VB
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" wrote: 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" wrote in message ... 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" wrote: 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/co...le_to_jpeg.htm NickHK "Karla" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to use VBA to save a copy of a workbook | Excel Discussion (Misc queries) | |||
Connection between MSSQL from/to Excel | Excel Programming | |||
Save copy of workbook | Excel Programming | |||
How can I save an editable copy of a protected excel workbook? | Excel Discussion (Misc queries) | |||
Database Filtering and Import or Copy to Remote Client Workbook | Excel Programming |