Trying to open a PDF in a folder

[Eric Penfold]

This code finds the folder fine but the line below says run time error 13?
Can`t see any mismatch errors please help.

SubPath = CStr(Val(Int(CmdData(0) / 50) * 50 + 1) & "-" & Int(CmdData(0) / 50 + 1) * 50"

Option Explicit
Public Sub OpenFile(strFilePath As String)
ShellExecute Application.Hwnd, "Open", strFilePath, 0&, 0&, SW_HIDE
End Sub
Private Sub Open_DrNo_Pdf_Click()

Dim SourcePath
Dim SubPath As String
Dim PdfPath As String
Dim CmdData
CmdData = Split(OpenDrawing.Value, "_")
CmdData(0) = Replace(CmdData(0), "_", "")
SourcePath = "\\dc01\Company\R&D\R & D Projects\Drawing Nos"
SubPath = CStr(Val(Int(CmdData(0) / 50) * 50 + 1) & "-" & Int(CmdData(0) / 50 + 1) * 50)
PdfPath = SourcePath & SubPath & "\" & Int(CmdData(0)) & "\" & OpenDrawing.Value & ".Pdf"
Call OpenFile(PdfPath)

End Sub

 

[GWteB]

Hi & welcome to MrExcel.

Error 13 is a type mismatch error, which means there's an attempt in the code to:
1. assigning a variable or property of type X to a variable or property of type Y, or ...
2. passing a variable of type X to a method or function whereas the invoked method or function expects a variable of type Y.

Try replacing this part of your code ...

SubPath = CStr(Val(Int(CmdData(0) / 50) * 50 + 1) & "-" & Int(CmdData(0) / 50 + 1) * 50)
PdfPath = SourcePath & SubPath & "\" & Int(CmdData(0)) & "\" & OpenDrawing.Value & ".Pdf"
Call OpenFile(PdfPath)

with this part:

SubPath = CStr(Val(CInt(CmdData(0) / 50) * 50 + 1) & "-" & CInt(CmdData(0) / 50 + 1) * 50)
PdfPath = SourcePath & SubPath & "\" & CmdData(0) & "\" & OpenDrawing.Value & ".Pdf"
Call OpenFile(PdfPath)

 

[Eric Penfold]

Thanks for your reply that part is working, problem now is the fact it can`t seem to open the pdf.
Says file not found but I know the path and

OpenDrawing.Value

is correct??

This line should open the Pdf??

Shell READERPath + " " + PDFFile, vbNormalFocus

Private Sub Open_DrNo_Pdf_Click()
Dim SourcePath As String
Dim SubPath As String
Dim PdfPath As String
Dim READERPath As String
Dim FName As String
Dim PDFFile As String
Dim PDFFilePath As String
Dim CmbData

CmbData = Split(OpenDrawing.Value, "-")
CmbData(0) = Replace(CmbData(0), "-", "")
SourcePath = "\\dc01\Company\R&D\R & D Projects\Drawing Nos"
SubPath = CStr(Val(Int(CmbData(0) / 50) * 50 + 1) & "-" & Int(CmbData(0) / 50 + 1) * 50)
PDFFilePath = SourcePath & "\" & SubPath & "\" & Int(CmbData(0))
FName = OpenDrawing.Value + ".Pdf"
PDFFile = PDFFilePath & "\" & FName
READERPath = "C:\Program Files (x86)\Adobe\Acrobat Reader DC\Reader\AcroRd32.exe"
Shell READERPath + " " + PDFFile, vbNormalFocus

End Sub

 

[GWteB]

Says file not found but I know the path and

OpenDrawing.Value

is correct??

Since you didn't share all your code we have to guess what the OpenDrawing object represents so there is no answer to that.

This line should open the Pdf??

Shell READERPath + " " + PDFFile, vbNormalFocus

Yes it should, but apparently something goes wrong in the trajectory in which the pathname and/or the filename is composed.
I would suggest following this process in the VBE's Immediate Window (press Ctrl+G) using the Debug.Print statement and the Step into ... key (F8).

CmbData = Split(OpenDrawing.Value, "-")
Debug.Print CmbData

CmbData(0) = Replace(CmbData(0), "-", "")
Debug.Print CmbData(0)

SourcePath = "\\dc01\Company\R&D\R & D Projects\Drawing Nos"
Debug.Print SourcePath

SubPath = CStr(Val(Int(CmbData(0) / 50) * 50 + 1) & "-" & Int(CmbData(0) / 50 + 1) * 50)
Debug.Print SubPath

PDFFilePath = SourcePath & "\" & SubPath & "\" & Int(CmbData(0))
Debug.Print PDFFilePath

FName = OpenDrawing.Value + ".Pdf"
Debug.Print FName

PDFFile = PDFFilePath & "\" & FName
Debug.Print PDFFile

 

[Eric Penfold]

This is working ok now until at the end it says run time error 5. In the Shell command.
The Me. OpenDrawing value is a Textbox value

VBA.Shell PdfFile & "\" & PdfFName, vbNormalFocus

Private Sub Open_DrNo_Pdf_Click()

Dim SourcePath As String
Dim SubPath As String
Dim PdfFile As String
Dim MyPath As String
Dim PdfFName As String
Dim CmbData

CmbData = Split(Me.OpenDrawing.Value, "-")
CmbData(0) = Replace(CmbData(0), "-", "")

MyPath = "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe"
SourcePath = "\\dc01\Company\R&D\R & D Projects\Drawing Nos"
SubPath = CStr(Val(Int(CmbData(0) / 50) * 50 + 1) & "-" & Int(CmbData(0) / 50 + 1) * 50)

PdfFName = OpenDrawing.Value & ".Pdf"
PdfFile = SourcePath & "\" & SubPath & "\" & Int(CmbData(0))
VBA.Shell PdfFile & "\" & PdfFName, vbNormalFocus



End Sub

 

[GWteB]

Although you've changed your code, the code is still composing your filename from multiple parts of which in one part even calculations are performed with at least one unknown value, the result of which determines the final path to the file.

Therefore I'm going to repeat myself:

I would suggest following this process in the VBE's Immediate Window (press Ctrl+G) using the Debug.Print statement and the Step into ... key (F8).

 

[Eric Penfold]

I have been trying F8 but still can`t get what is happening
I have a folder set up like this 10001-10050/10001 as you can see it`s in 50 internal folder increments
Trust that makes sense

 

[Eric Penfold]

Although you've changed your code, the code is still composing your filename from multiple parts of which in one part even calculations are performed with at least one unknown value, the result of which determines the final path to the file.

Therefore I'm going to repeat myself:

Please help

 

[GWteB]

I would recommend that you become a little more familiar with using the VBE and therefore read this article thoroughly.

https://www.myonlinetraininghub.com/debugging-vba-code

 

[alansidman]

Are you comitted to VBA. Power Query is probably the easiest way to get a PDF into Excel. Since you have O365 it is called Get and Transform Data and found on the Data Tab.