Saved for later reference

online repository of stuff I had to google for hours to figure out – and random snippets of code

“Microsoft Dynamics CRM for Outlook with Offline Access has already been configured for a user on this computer. Only one use can be configured per computer for Microsoft CRM for Outlook with Offline Access.”

Tags: , , ,

If you try to swap users on a laptop, with Dynamics CRM installed in Offline Access mode, it will give the error above; searching gave me this page from the Chinese Dynamics CRM team, so I’m just going to repeat it here:

1
2
3
4
  C:\>osql –E –S localhost\CRM
      1> drop database mscrm_msde
      2> go
      1> quit

Basically, you connect to the SQL Server on the local computer, and delete the Offline Access database, allowing you to reconfigure as normal. Note that this won’t let you reconfigure the user as “online only” – the new configuration will automatically be Offline Access.

Secondly, this works even if the user is not Local Administrator on the computer.

Share

Printing to PDF from VBA in Excel

Tags: , , , , ,

Creating a PDF from Excel typically requires printing to the “Adobe PDF” printer, selecting the location and file name to save the .pdf as, and finally getting the newly created PDF opened in Acrobat. Automating this is not very straight-forward, and took quite a while to get “right”.

Background

There is no direct COM API that can be used to tell Acrobat to “convert this sheet to PDF please”, and all the solutions I’ve found on the net have been missing pieces of the puzzle (i.e. lots of hardcoded values, only works on the computer they were made on, or requires you to do manual steps before you can start.)

The most common manual step is that you have to uncheck “Don’t send fonts to Adobe PDF”, or as it’s called in recent versions of Acrobat:

Adobe PDF setting: Use system fonts

Adobe PDF setting: Use system fonts

The problem here is that this setting is in no way persistent, often resetting to its checked state – and there is no registry setting or .ini file setting to force it unchecked.

What you’ll need

As usual, these are the specific versions I use – other versions should work just as well.

  • Excel 2003
  • Adobe Acrobat 8
  • A printer with a PostScript driver (you can also install a dummy printer if you don’t have a “real” printer)

The code

Some of this code is based on this PCreview.co.uk forums post (I can’t remember if this was my original source, but it’s the closest I can find)

You’ll need the file cAcroDist.cls, a class that handles the Distiller events – either import the linked file, or create a new class in VBA and paste the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Option Explicit

Public WithEvents odist As PdfDistiller
Attribute odist.VB_VarHelpID = -1
Dim StartTime As Date

Private Sub Class_Initialize()
 Set odist = New PdfDistiller
End Sub

Private Sub odist_OnJobDone(ByVal strInputPostScript As String, ByVal strOutputPDF As String)
 Kill strInputPostScript ' deletes the input postscript, since it's not needed after PDF is made
End Sub

Private Sub odist_OnJobFail(ByVal strInputPostScript As String, ByVal strOutputPDF As String)
End Sub

Private Sub odist_OnJobStart(ByVal strInputPostScript As String, ByVal strOutputPDF As String)
End Sub

On to the PDF producing code – either use the code below, or import this handy pre-made module: PrintToPDF.bas (note that this needs some manual changes).

In a new module, create the following Sub:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub PrintToPDF(sPDFFileName, SheetName)

 Dim sPSFileName As String 'Name of PS to be created
Dim sJobOptions As String
 Dim sCurrentPrinter As String 'Same current printer choice to resume at end
Dim sPDFVersionAndPort As String 'Version of Adobe
Dim sDummyPrinter As String ' Need a dummy printer to produce the PS
Dim appDist As cAcroDist

 Set appDist = New cAcroDist
 sCurrentPrinter = Application.ActivePrinter 'Save the currently active printer
sDummyPrinter = NetworkPrinter("Dummy Printer Name") ' Change this to match an installed PS-capable printer driver
Application.ActivePrinter = sDummyPrinter
 sPSFileName = ThisWorkbook.Path & ThisWorkbook.Name & ".ps"  'Name of PS file
ThisWorkbook.Sheets(SheetName).PrintOut ActivePrinter:=sDummyPrinter, _
 PrintToFile:=True, PrToFileName:=sPSFileName 'Prints to PS

 Call appDist.odist.FileToPDF(sPSFileName, sPDFFileName, sJobOptions) 'Creates PDF

 On Error Resume Next
 Kill sPSFileName 'Removes PS
On Error GoTo 0

 Application.ActivePrinter = sCurrentPrinter 'Change back to the original printer
End Sub

This procedure finds a specifically named printer, uses its driver to produce a PostScript file, then uses Acrobat Distiller to convert the .ps to .pdf. You will need to add a reference to Acrobat Distiller manually in the VBA editor.

Finally, we need the NetworkPrinter function, which locates which port a printer is on – you CAN hardcode sDummyPrinter to something like “HP Deskjet on Ne03:”, but that will only work on computers that have the printer on that exact port. The NetworkPrinter function – slightly modified solves this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Dim Prt_On As String

Function NetworkPrinter(ByVal myprinter As String)
 On Error Resume Next

 Dim NetWork As Variant
 Dim X As Integer
 Prt_On = " On "
'/// Define NetWork Array \\\
NetWork = Array("Ne00:", "Ne01:", "Ne02:", "Ne03:", "Ne04:", _
 "Ne05:", "Ne06:", "Ne07:", "Ne08:", _
 "Ne09:", "Ne10:", "Ne11:", "Ne12:", _
 "Ne13:", "Ne14:", "Ne15:", "Ne16:", _
 "LPT1:", "LPT2:", "File:", "SMC100:", _
 "XPSPort:")
 'Setup printer to Print
X = 0
TryAgain:
 On Error Resume Next
 'Printer
Application.ActivePrinter = myprinter & Prt_On & NetWork(X)
  If Err.Number <> 0 And X < UBound(NetWork) Then
    X = X + 1
    GoTo TryAgain
  ElseIf Err.Number <> 0 And X > UBound(NetWork) - 1 Then
 GoTo PrtError
 End If
 On Error GoTo 0
 NetworkPrinter = myprinter & Prt_On & NetWork(X)
errorExit:
 Exit Function
PrtError:
 'no printer found
NetworkPrinter = ""
 Resume errorExit
End Function

This is also defined in PrintToPDF.bas linked above.

Example use

To convert the “Report” sheet in the currently active workbook to a PDF, use this code:

1
PrintToPDF "C:\MyReport.pdf", "Report"

Read the rest of this entry »

Share

© 2009 Saved for later reference. All Rights Reserved.

This blog is powered by Wordpress and Magatheme by Bryan Helmig.