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
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)
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.