PDA

View Full Version : how can i open external excel file by using button in userform



kofatoto
09-10-2017, 09:59 PM
hello
I always find help from you
so i want open external excel file by using button in userform

i used this code


Private Sub CommandButtonopen_Click()
On Error Resume Next
Dim MyFile As String

MyFile = Application.GetOpenFilename()

Dim app As Object
Set app = CreateObject("Shell.Application")

app.Open (MyFile)

End Sub

this code open all file for example (jpg , word , txt ) but not open xls (excel files)
how can i open excel files
thanks for help

kofatoto
09-10-2017, 10:26 PM
i edit the code to

Private Sub CommandButtonff_Click()
On Error Resume Next
Dim MyFile As String
Dim pro As Workbook

MyFile = Application.GetOpenFilename("All Files (*.*), *.*")

Dim app As Object

Set app = CreateObject("Shell.Application")
Set pro = Workbooks.Open(MyFile)
app.Open (MyFile)
pro.Open (MyFile)

End Sub

it open excel file but i cant edit it or close

mancubus
09-11-2017, 02:17 AM
Private Sub CommandButtonff_Click()


Dim FileToOpen As String
Dim wb As Workbook

On Error Resume Next
MyFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*")

If FileToOpen <> False Then
Set wb = Workbooks.Open(FileToOpen)
Else
MsgBox "Quitting... file to open not selected..."
Exit Sub 'stop macro here, if the user does not select a file
End If

wb.Worksheets("MySheetName").Range("A1").Value = "Test" 'to edit a cell A1 in sheet MySheetName
wb.Close SaveChanges:=True 'save and close the opened workbook


End Sub

kofatoto
09-11-2017, 03:36 AM
sorry
not working

GTO
09-11-2017, 05:03 AM
Try:



Option Explicit

Private Sub CommandButton1_Click()
Dim sFileName As String
Dim WB As Workbook

sFileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*")

If sFileName = "False" Then
MsgBox "No file picked", vbExclamation, vbNullString
Exit Sub
End If

Set WB = Workbooks.Open(sFileName)

End Sub


Hope that helps,

Mark

mancubus
09-11-2017, 06:16 AM
wb.Worksheets("MySheetName").Range("A1").Value = "Test"
take into account that the above line is for demonstration purposes only.

change MySheetName, A1 and Test to suit.

you can upload your workbook showing the desired output. (see my signature)

GTO
09-11-2017, 08:24 AM
Hi Mancubus,

I believe the typo may have confused the OP.

Dim FileToOpen As String
MyFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*")

Mark

kofatoto
09-11-2017, 10:50 AM
my file :

http://www.mediafire.com/file/1rjbf2rer2r4ajc/kofa_ver_2.xlsm

kofatoto
09-11-2017, 10:53 AM
all codes opened the external excel file but the problem is
1- how can i open any file (jpg , word , excel , txt )
2-when excel file opened how can i edit it in same time userform showing

excuse me for my bad English language

my file :

http://www.mediafire.com/file/1rjbf2...ofa_ver_2.xlsm



(http://www.mediafire.com/file/1rjbf2rer2r4ajc/kofa_ver_2.xlsm)

mancubus
09-11-2017, 01:12 PM
Dim FileToOpen As String
MyFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*")

Mark

thanks for correcting Mark. :hi:
i sometimes unnecessarily try to be more descriptive when naming variables. :banghead:

kofatoto
09-12-2017, 12:14 AM
the problem is
when i open developer tab and run my project , the cod work fine
but when i save the project and run final file , the code not working
any help please

kofatoto
09-12-2017, 09:28 AM
a part of souluation
i create 2 buttons
the first button code is

' this code open any file
On Error Resume Next
Dim MyFile As String
MyFile = Application.GetOpenFilename()
Dim app As Object
Set app = CreateObject("Shell.Application")
app.Open (MyFile)

the second button code is

' this code open excel file
On Error Resume Next
Dim MyFile As String
Dim xl As New Excel.Application
MyFile = Application.GetOpenFilename("All Files (*.*), *.*")
xl.Workbooks.Open (MyFile)
xl.Visible = True


how can i merge 2 codes into one button

mancubus
09-14-2017, 11:36 PM
you can use the first button's code to open the MS office files as well.