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

Pätkis

New Member
Joined
Jan 30, 2019
Messages
11
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)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,144,571
Messages
5,725,059
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top