macro code for opening excel file

[Syed Azeem]

macro code for opening excel file

 

[Syed Azeem]

Sorry for not being specific.

Can someone please help me with the macro code for opening excel file from a folder

Thanks in advance

Syed Azeem

 

[DocAElstein]

Hello Syed Azeem
Welcome to the Board

Sorry for not being specific..

Yes you are not very specific!. Please do try to be a lot more specific

...
Can someone please help me with the macro code for opening excel file from a folder
....

This code will call up the Dialogue box which allows you to select the File you wish to open

If you wish a particular Folder to come up in the Dialogue box, you should modify Line 20. You should Let strDefPath = The Full File Path to the Folder you are interested in

Code:

[color=darkgreen]'[/color]
[color=blue]Sub[/color] GetFileSyedAzeemOpenIt() 'http://www.mrexcel.com/forum/excel-questions/918955-macro-code-opening-excel-file.html
10  [color=darkgreen]'perform a ChDir before the GetOpenFilename #### CHANGE strDefPath to suit the Path to folder you are interested in[/color]
20  [color=blue]Dim[/color] strDefPath [color=blue]As[/color] String: [color=blue]Let[/color] strDefPath = ThisWorkbook.path [color=darkgreen]'#### Any Path / Folder to test this code! here we simply use the Path where the File with this code in is[/color]
30  [color=blue]Dim[/color] strMyDrive [color=blue]As[/color] String: [color=blue]Let[/color] strMyDrive = Left(strDefPath, 2) [color=darkgreen]', the drive (  C:   E:   etc   )[/color]
40  ChDrive (strMyDrive): ChDir (strDefPath) [color=darkgreen]'Changing the Drive and Directory may be needed for check of Filename to work[/color]
50
60  [color=darkgreen]'Application.GetOpenFilename[/color]
70  [color=blue]Dim[/color] StrOpenFileTypesDrpBx [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'The Drop Box options in GetOpenFilename Dialogue Window[/color]
80  [color=blue]Let[/color] StrOpenFileTypesDrpBx = "Excel (*.xlsx),*.xlsx,OpenOffice (*.ods),*.ods,All Files (*.*),*.*,ExcelMacros (*.xlsm),.xlsm"
90             [color=darkgreen]'"DrpBxMsg1(DisplydFileType1),*.DisplydFileType1  ,  DrpBxMsg1(DisplydFileType2),*.DisplydFileType2   ,    DspBxMsg3(..etc...."[/color]
100 [color=blue]Dim[/color] FullFilePathAndName [color=blue]As[/color] [color=blue]Variant[/color]  [color=darkgreen]'Complete Path on Computer string.  String is mostly OK, but Variant allows for "False" answer below.[/color]
110 [color=blue]Let[/color] FullFilePathAndName = Application.GetOpenFilename(StrOpenFileTypesDrpBx, 1, "Name up left in Dialogue box", , [color=blue]False[/color]) [color=darkgreen]'All optional Arguments[/color]
120   [color=darkgreen]''("DrpBxMsg1(DisplydFileType1),*.DisplydFileType1 ,  DspBxMsg2(..etc....",   Default DrpBx Index   ,      "DialogueWindow(Form)Name" ,   Button-Mac Only     ,  MultipleFileSelectionOption  )[/color]
130       [color=blue]If[/color] FullFilePathAndName = [color=blue]False[/color] [color=blue]Then[/color] [color=darkgreen]'Application.GetOpenFilename returns Boolean False for no File selection.[/color]
140       MsgBox "You did't select a file!", vbExclamation, "Canceled"
150       [color=blue]Exit[/color] [color=blue]Sub[/color] [color=darkgreen]' user cancelled, so get out of Sub[/color]
160       [color=blue]Else[/color]
170       [color=blue]End[/color] [color=blue]If[/color]
180 [color=darkgreen]'[/color]
190 'Open File, Play around a bit with the name and path strings
200 Workbooks.Open Filename:="" & FullFilePathAndName & ""
210 [color=blue]Dim[/color] FullNameOnly [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'File name as typically seen displayed with last bit after dot[/color]
220 [color=blue]Let[/color] FullNameOnly = Right(FullFilePathAndName, Len(FullFilePathAndName) - InStrRev(FullFilePathAndName, "\")) [color=darkgreen]'Full File including extension ( Bit after .  Dot )[/color]
230 [color=blue]Dim[/color] NameOnly [color=blue]As[/color] [color=blue]String[/color]
240 [color=blue]Let[/color] NameOnly = Left(NameOnly, (InStrRev(NameOnly, ".") - 1)) [color=darkgreen]'To Take off the bit after the . dot[/color]
250
260         [color=darkgreen]'    'Normally you might want to do stuff here[/color]
270         [color=darkgreen]'[/color]
280         '    'Close File
290        [color=darkgreen]'    Workbooks("" & NameOnly & ".xls").Close 'When referrencing Worksheets collection always use the bit after the . dot ....Rory:  ...... when you pass a workbook name to the Workbooks collection you should always include the file extension.   http://www.mrexcel.com/forum/excel-questions/899597-visual-basic-applications-loop-through-sheet-copy-contents-new-workbook.html?#post4335006[/color]
[color=blue]End[/color] [color=blue]Sub[/color] [color=darkgreen]'GetFileAlanOpenIt()[/color]

Alan

 

[DocAElstein]

Here is another way from Kenneth Hobson
http://www.mrexcel.com/forum/excel-questions/574110-getopenfilename-default-directory.html

[color=blue]Sub[/color] Test() [color=darkgreen]'http://www.mrexcel.com/forum/excel-questions/574110-getopenfilename-default-directory.html    http://www.mrexcel.com/forum/excel-questions/371814-visual-basic-applications-getopenfilename.html    http://www.mrexcel.com/forum/excel-questions/565906-setting-default-path-application-getopenfilename.html     http://www.mrexcel.com/forum/excel-questions/17968-getopenfilename-current-workbook-path-not-default.html[/color]
  MsgBox FileOpen("C:\", "Documents", "*.xls; *.xlsx; *.xlsm")
[color=blue]End[/color] [color=blue]Sub[/color]


[color=blue]Function[/color] FileOpen(initialFilename [color=blue]As[/color] [color=blue]String[/color], _
  [color=blue]Optional[/color] sDesc [color=blue]As[/color] [color=blue]String[/color] = "Excel (*.xls)", _
  [color=blue]Optional[/color] sFilter [color=blue]As[/color] [color=blue]String[/color] = "*.xls") [color=blue]As[/color] [color=blue]String[/color]
  [color=blue]With[/color] Application.FileDialog(msoFileDialogOpen)
    .ButtonName = "&Open"
    .initialFilename = initialFilename
    .Filters.Clear
    .Filters.Add sDesc, sFilter, 1
    .Title = "File Open"
    .AllowMultiSelect = [color=blue]False[/color]
    [color=blue]If[/color] .Show = -1 [color=blue]Then[/color] FileOpen = .SelectedItems(1)
  [color=blue]End[/color] [color=blue]With[/color]
[color=blue]End[/color] [color=blue]Function[/color]

( A quick Google through MrExcel Threads is always worth a go ! )

I do not understand exactly how this code works. But I think as always with these sorts of things there is usually a “File Systems Object” Alternative way of doing it. And the above code is, I believe, an example of that

Alan

 

[DocAElstein]

Oops - there was a typo at line 240 on my code

here you go - corrected

Sub GetFileSyedAzeemOpenIt() 'http://www.mrexcel.com/forum/excel-questions/918955-macro-code-opening-excel-file.html
10  'perform a ChDir before the GetOpenFilename #### CHANGE strDefPath to suit the Path to folder you are interested in
20  Dim strDefPath As String: Let strDefPath = ThisWorkbook.path '#### Any Path / Folder to test this code! here we simply use the Path where the File with this code in is    You may need to change the drive before the path can be changed, the following does both.
30  Dim strMyDrive As String: Let strMyDrive = Left(strDefPath, 2) ', the drive (  C:   E:   etc   )
40  ChDrive (strMyDrive): ChDir (strDefPath) 'Changing the Drive and Directory may be needed for check of Filename to work
50
60  'Application.GetOpenFilename
70  Dim StrOpenFileTypesDrpBx As String 'The Drop Box options in GetOpenFilename Dialogue Window
80  Let StrOpenFileTypesDrpBx = "Excel (*.xlsx),*.xlsx,OpenOffice (*.ods),*.ods,All Files (*.*),*.*,ExcelMacros (*.xlsm),.xlsm"
90             '"DrpBxMsg1(DisplydFileType1),*.DisplydFileType1  ,  DrpBxMsg1(DisplydFileType2),*.DisplydFileType2   ,    DspBxMsg3(..etc...."
100 Dim FullFilePathAndName As Variant  'Complete Path on Computer string.  String is mostly OK, but Variant allows for "False" answer below.
110 Let FullFilePathAndName = Application.GetOpenFilename(StrOpenFileTypesDrpBx, 1, "Name up left in Dialogue box", , False) 'All optional Arguments
120   ''("DrpBxMsg1(DisplydFileType1),*.DisplydFileType1 ,  DspBxMsg2(..etc....",   Default DrpBx Index   ,      "DialogueWindow(Form)Name" ,   Button-Mac Only     ,  MultipleFileSelectionOption  )
130       If FullFilePathAndName = False Then 'Application.GetOpenFilename returns Boolean Fals'e for no File selection.
140       MsgBox "You did't select a file!", vbExclamation, "Canceled"
150       Exit Sub ' user cancelled, so get out of Sub
160       Else
170       End If
180 '
190 'Open File, Play around a bit with the name and path strings
200 Workbooks.Open Filename:="" & FullFilePathAndName & ""
210 Dim FullNameOnly As String 'File name as typically seen displayed with last bit after dot
220 Let FullNameOnly = Right(FullFilePathAndName, Len(FullFilePathAndName) - InStrRev(FullFilePathAndName, "\")) 'Full File including extension ( Bit after .  Dot )
230 Dim NameOnly As String
240 Let NameOnly = Left(FullNameOnly, (InStrRev(FullNameOnly, ".") - 1)) 'To Take off the bit after the . dot
250
260         '    'Normally you might want to do stuff here
270         '
280         '    'Close File
290        '    Workbooks("" & NameOnly & ".xls").Close 'When referrencing Worksheets collection always use the bit after the . dot ....Rory:  ...... when you pass a workbook name to the Workbooks collection you should always include the file extension.   http://www.mrexcel.com/forum/excel-questions/899597-visual-basic-applications-loop-through-sheet-copy-contents-new-workbook.html?#post4335006
End Sub 'GetFileSyedAzeemOpenIt()
'

 

[DocAElstein]

Hi, ......

Just to complete the ways of doing this ( although I guess there may be more, there usually are!!).

The next Method I was coming across in another thread, but thought appropriate to tack on here....
It uses the “Shell Objects”

I do not understand this fully yet, but a bit goggling should get Yous up to date. The Shell Object is I think something to do with a collection of stuff to do with Windows. Often the literature says it is to do with all Open Windows: But that does not quite tie up with what I do with it here, as I am using it to open a Window. But I have also seen it described as “for Scripting”. This reminds of the Microsoft Scripting Runtime Library, which generally allows getting at File information.

So it follows maybe, that with “Shell Objects” is a “BrowseForFolder” Method, which as you may guess means it lets you go through and select a folder. ( I had a feeling it will either return a string of the Folder name you choose, or an object which is that chosen Folder. I Declared separately for both, and got both. )

This “Dialogue Box” approach is very close to Our original Method
Application.GetOpenFilename
and second method
Application.FileDialog(msoFileDialogOpen)
which are basically giving you a “Pop up” box to select a File from.
Theis “Shell Objects”.BrowseForFolder Methoid is therefore a comparative “pop up” box approach.
But it is differing in that you can only choose a Folder.
So The code then has a bit using the Dir-Funktion to step through all the files in that folder. A Normal “pop up” box then asks at each File if you want to open it.

Anyway so a third code, not fully commented as I do not understand it all yet. It can be simplified greatly, but in this form it may help to understand it

Just a quick Summary
_1) Post # 5 “Application.GetOpenFilename” Way

_2) Post #4 “Application.FileDialog(msoFileDialogOpen)” Way

_3) Post #6 ( Here) “ "Shell Objects".BrowseForFolder with Dir Loop” Way

Code _3:

'Code 3: "Shell Objects".BrowseForFolder in conjunction with a Dir Loop Bit to go through all Files "pop up" box approach
Sub Code3_Update() '

Dim strDefPath As String: Let strDefPath = ThisWorkbook.path ' Any Path / Folder to test this code! here we simply use the Path where the File with this code in is

Dim ShellApp As Object 'These two lines are an Example of Late Binding". When using CreateObject, the registry is searched for a program identifier. You are creating a latebound reference to an application object returned by the "Application" property of the "Shell" object which is contained in the "Shell32" library.
Set ShellApp = CreateObject("Shell.Application") '...is creating a "IShellDispatch4" object. Depending on your version, this may be IShellDispatch3 or IShellDispatch2. To see the properties and methods of this object, you will need to right click within your object browser and select, "Show Hidden Members".
'ShellApp  As Shell32.Shell ' The next two lines are the equivalent "Early Binding pair"
'Set ShellApp = New Shell32.Shell ''You will need to do select form VB Editor options .. Extras...then scroll down to  Microsoft Shell Controls And Automation  ...  and add a check

Dim objWB As Object, strWB As String 'The  .BrowseForFolder Method appears either return a string of the Folder name you choose, or an object which is that chosen Folder, depending on how you declare the variable to put the retuned "thing" in... so after doing these two sorts of declaration .......
Set objWB = ShellApp.BrowseForFolder(0, "Please choose a folder", 0, "" & strDefPath & "") 'An Object of Folder taype returned
Let strWB = ShellApp.BrowseForFolder(0, "Please choose a folder", 0, "" & strDefPath & "") 'A string of the name of the Folder is returned
Dim path As String: Let path = objWB.self.path & Application.PathSeparator 'or ShellApp.self.Path & "\"
Dim strFile As String: strFile = Dir(path & "*.xls*") 'The Dir() Methood with a given argument returns the first file it finds meeting the search criteria of the argument. ( Here we look for all files which have .xls as the first 3 characters of the extension, in other words all Excel Files
    Do While Len(strFile) > 0 ' a zero length string will be returned if nothing is found. This will be when all have been gone through, so we would not want to procede furthet with the code. So not equal to this is a convenient condition to no longer do. It is commanly believed that a len() check is quicker that ="" or = vbNullString, but it may not be...   http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4411660
    Dim Response As Integer 'In VBA Butons "yes is 6, 7 is "no"
    Response = MsgBox(prompt:="Do you want to open " & strFile & "?", Buttons:=vbYesNo, Title:="File Check") ' Displays a message box with the yes and no options.
        If Response = vbYes Then 'Do nothing, that is to say just carry on after End of If
        'Workbooks.Open Filename:="" & Path & "" & "" & StrFile & "": Debug.Print Path & StrFile' This bit is just a line that could be helpful in Debugging
        Workbooks.Open(path & strFile).Activate
        ' YOUR CODE HERE if you had one to do anything
        'ActiveWorkbook.Close SaveChanges:=True' Save with changes if you did wanted to etc. Also savechanges:=True 'Option stops automatically being asked so no need for Application.DisplayAlerts = False / True pair . And we want to save or the two date alterations will not be made
        Else 'We come here if you did nnot indicate you wanted to open the File. So we End The If Then and ...
        End If
    strFile = Dir '....'Using simple unqualified Dir goes to next File similar in type to the last in last Folder looked. So this gives us the next File to look at.
    Loop
Set ShellApp = Nothing 'Most people think this is unecerssary in VBA, but I still think maybe just to be sure it is OK to do....http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4414065
End Sub
'Ref
'http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html
'http://www.excelforum.com/excel-programming-vba-macros/1126564-choose-a-folder-and-loop-a-sub.html#post4315748
'
'http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/
'http://www.mrexcel.com/forum/general-excel-discussion-other-questions/223232-set-shell-%3D-createobject-shell-application.html
''Library Shell32   Tom Schreiner
'C:\WINDOWS\system32\SHELL32.dll
'Microsoft Shell Controls And Automation
'Open up your VBA IDE and set a reference to "Microsoft Shell Controls And Automation"
'Now open your object browser and select "Shell32".
'Under "Classes", select "Shell"
'Note that one of the properties, to the right, is an "Application" property. This property returns an object reference to the windows shell.
'When using CreateObject, the registry is searched for a program identifier. You are creating a latebound reference to an application object returned by the "Application" property of the "Shell" object which is contained in the "Shell32" library.
'Your code, as is, is creating a "IShellDispatch4" object. Depending on your version, this may be IShellDispatch3 or IShellDispatch2. To see the properties and methods of this object, you will need to right click within your object browser and select, "Show Hidden Members".
'Anyway, your code and what it is doing will become more clear to you if you early bind. That is, set a reference beforehand so the compiler knows what's up ahead of time... Also, the properties and methods will show up via intellisense as you type and any events will now be available to you as well.
'Both return references to a "Shell" object.

Alan..