Big_Al_fae_the_rigs
New Member
- Joined
- Oct 31, 2024
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
Hi,
I'm trying to learn how to better use VBA and Macros to help me and my colleagues with work.
What I am trying to achieve is a workbook with multiple sheets for separate systems and on each sheet, individual buttons which correspond to a specific technical drawing.
The drawings are in packs saved in pdf format, so the code for each button needs to open the correct pdf to the relevant page. I have managed to achieve this step.
However, when I then click a different button which is linked to a different page in the same pdf the code fails (my understanding being that it is not possible to have the same pdf file open multiple times). I searched for a solution for the sub to check if the file was open (if not open, then open as normal) and if it was already open simply change the page which is displayed, but this has proved fruitless so far. Instead of this, I thought a slightly more crude solution would be to perform the same check to see if the file was open (again if not open, then open as normal) and if it was already open then close the file before reopening to the page required.
I have managed (through trial and error and liberal borrowing of code I have managed to find online for parts of the problem) to get the check if file is open to work, and the [if not open, then open] part to work, and have even finally managed to get the [if open, then close] working, but no matter what I have tried so far, I cannot get it to close the file and then re-open to the new required page.
This is the code I have so far.....
Option Explicit
Private Const cAdobeReaderExe As String = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
Function IsFileOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error ErrNo
End Select
End Function
Private Sub ClosePDF()
Shell "taskkill.exe /f /t /im AcroRd32.exe"
End Sub
Private Sub CommandButtonTest_Click()
Dim Ret
Ret = IsFileOpen("filepath.pdf")
Dim PDFFile As String
PDFFile = "filepath.pdf"
Dim AdobeCommand As String
AdobeCommand = " /a ""page=1=Open Actions"" "
If Ret = True Then
Call ClosePDF
Shell cAdobeReaderExe & AdobeCommand & Chr(34) & PDFFile & Chr(34), vbNormal
Else
Shell cAdobeReaderExe & AdobeCommand & Chr(34) & PDFFile & Chr(34), vbNormal
End If
End Sub
I'm trying to learn how to better use VBA and Macros to help me and my colleagues with work.
What I am trying to achieve is a workbook with multiple sheets for separate systems and on each sheet, individual buttons which correspond to a specific technical drawing.
The drawings are in packs saved in pdf format, so the code for each button needs to open the correct pdf to the relevant page. I have managed to achieve this step.
However, when I then click a different button which is linked to a different page in the same pdf the code fails (my understanding being that it is not possible to have the same pdf file open multiple times). I searched for a solution for the sub to check if the file was open (if not open, then open as normal) and if it was already open simply change the page which is displayed, but this has proved fruitless so far. Instead of this, I thought a slightly more crude solution would be to perform the same check to see if the file was open (again if not open, then open as normal) and if it was already open then close the file before reopening to the page required.
I have managed (through trial and error and liberal borrowing of code I have managed to find online for parts of the problem) to get the check if file is open to work, and the [if not open, then open] part to work, and have even finally managed to get the [if open, then close] working, but no matter what I have tried so far, I cannot get it to close the file and then re-open to the new required page.
This is the code I have so far.....
Option Explicit
Private Const cAdobeReaderExe As String = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
Function IsFileOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error ErrNo
End Select
End Function
Private Sub ClosePDF()
Shell "taskkill.exe /f /t /im AcroRd32.exe"
End Sub
Private Sub CommandButtonTest_Click()
Dim Ret
Ret = IsFileOpen("filepath.pdf")
Dim PDFFile As String
PDFFile = "filepath.pdf"
Dim AdobeCommand As String
AdobeCommand = " /a ""page=1=Open Actions"" "
If Ret = True Then
Call ClosePDF
Shell cAdobeReaderExe & AdobeCommand & Chr(34) & PDFFile & Chr(34), vbNormal
Else
Shell cAdobeReaderExe & AdobeCommand & Chr(34) & PDFFile & Chr(34), vbNormal
End If
End Sub
