Wednesday, February 29, 2012

PDF Bookmarks: Change Zoom level and Name of PDF Bookmarks

If you are wanting to change the Bookmark zoom level on a bunch of your PDF bookmarks in Adobe Acrobat, or just modify the name of your bookmarks, here is the easy way to do it:

Use JPDFBookmarks:

And follow these instructions:

  1. Modify one of your bookmarks to inherit the zoom (or choose whatever type of zoom you want)
  2. Download JPDFBookmarks
  3. Open the zip file and extract the files, then open JPDFBookmarks.
  4. Open your PDF file in JPDFBookmarks.
  5. Dump the bookmark data to a text file using JPDFBookmarks with the command Tools>Dump.
  6. Find the bookmark you edited (in the text file that opens,) copy the correct portion of the text associated with your edited bookmark that is different from all the other bookmarks, such as TopLeftZoom,0,0,0.0, and replace the other setting on all the other bookmarks, such as FitWidth, -6,  using Find and Replace in Notepad.
  7. Change the name of any bookmark as well, by editing the name of the bookmark.
  8. Save the changes to the text file.
  9. In JPDFBookmarks, load the bookmark data back using Tools>Load
  10. Save the file in JPDFBookmarks. 
  11. Open the PDF in Adobe Acrobat, and the bookmarks are fixed.

This is a great open source solution for working with PDF bookmarks, and changing the zoom level of existing PDF bookmarks.

See also:


Rename many PDF Bookmarks
Inherit Zoom
Fit Page
Actual Size
Fit Width
Fit Visible
Named Destination
pdf bookmarks inherit zoom
dozens hundreds thousands many bunch
edit PDF bookmarks

Thursday, January 5, 2012

Get data from a SQL Server Stored Procedure into Excel automatically


Do you want to get data from a stored procedure in SQL Server and have it drop right into MS Excel automatically? I recently needed just that, so follow this simple tutorial to get the job done right away...

How to run a SQL Stored Procedure from Excel without using VBA, and even include dynamic parameters that can be stored in cells in Microsoft Excel!!!

To pull data from a SQL Server Stored Procedure in Excel using parameters, do the following:

Excel 2007 Instructions:

1.  Select the Data tab on Excel's Ribbon, then within the Get Exernal Data group choose the "From other Sources" drop-down.  Then Choose "From Microsoft Query"

2.  Within "Choose Data Source" pop-up box, select your SQL Server, then hit OK.

3.  Close the "Add Tables" popup if necessary.

4.  Click on the "SQL" button, or choose View > SQL to open the SQL pop-up editor.

5.  Enter the following syntax:  {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}

    For example:  {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}

    Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.

6.  Hit the OK button. A question box should pop-up saying "SQL Query can't be represented graphically, continue anyway?", just hit the OK button.

7.  You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.

8.  Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.

9.  You should now be looking at an "Import Data" pop-up. Click the Properties button, which will bring up the "Connection Properties" pop-up.

10.  Select the Definition tab, then select the Parameters button. You should now see a "Parameters" pop-up, where you can connect the parameter to a specific cell.

11.  Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.

12.  If you want the data to refresh every time you change the cell containing the parameter, check the box stating "Refresh automatically when cell value changes"

13.  Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.

14.  You should now have some data straight from your stored procedure.

Hope this helps someone out there!

Here are some common error messages that you may experience when trying to do this...

     No value given for one or more required parameters.

     Parameters are not allowed in queries that can't be displayed graphically.

     Could not find stored procedure ...    

Tags and Common Search Phrases:

connect excel to stored procedure

Excel Get external data from stored procedure
Microsoft query to SQL Server stored procedure from excel
get data into excel from stored procedure
add parameters to excel for stored procedure