VBA: Open file with same name than sheet is named & user can select sheets what to copy

[Pätkis]

Hi,

I have a macro, which actives certain sheet and then open pdf file, which information is copied to this certain sheet. For every day there is one sheet and one file, which needs to be copied.
Is it possible to open pdf file with same name than sheet is named? How could I do it so that user could select days what to copy to excel?


Sub pdf_To_Excel_Adobe2()

Dim myWorksheet As Worksheet
Dim adobeReaderPath As String
Dim pathAndFileName As String
Dim shellPathName As String
Dim fileName As String


Sheets("2").Select

'Opens day 2 and copies data from PDF
Set myWorksheet = ActiveWorkbook.Worksheets("2")
With myWorksheet
.Range("A:M").Clear
End With

adobeReaderPath = "C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
pathAndFileName = ActiveWorkbook.Path & "\2.pdf"
shellPathName = adobeReaderPath & " """ & pathAndFileName & """"

Call Shell( _
pathname:=shellPathName, _
windowstyle:=vbNormalFocus)

Application.Wait Now + TimeValue("0:00:02")

SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"

Application.Wait Now + TimeValue("0:00:05")

With myWorksheet
.Range("a1").Select
.PasteSpecial Format:="Text"
End With

Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)