Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



To Show Both .xls And .xlsx Files While Opening

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have created a macro that opens other microsoft excel spreadsheet files. I want that when the user browse for the open file then the window that opens shud display both .xls and xlsx files at once. the code i ve used is below:

Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
Finfo = "Workbook (*.xls),*.xls," & "Office 2007 Excel Workbook (*.xlsx),*.xlsx,"
' Display *.* by default
FilterIndex = 1
' Set the dialog box caption
Title = "Select a File to Import"
' Get the filename
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)
' Handle return info from dialog box
If FileName = False Then
MsgBox "No file was selected."
Else
'MsgBox "You selected " & FileName
' opens an the excel file at the location: FileName
Workbooks.Open (FileName)

View Answers     

Similar Excel Tutorials

Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
Save the Current Worksheet as a New Excel Workbook File
This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to ...
Reference Other Excel Files with Formulas and Functions
In Excel you can use formulas and functions to reference data that is stored in another Excel workbook.  This creat ...
Excel Macro to Save a Specific Worksheet as a New File
This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be ...

Helpful Excel Macros

Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Open a PowerPoint Presentation from Excel
- This free macro for Microsoft Excel allows you to open any PowerPoint presentation from excel. You can change the file
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

Similar Topics







Hi Excel Guru's,

I am very new to VBA coding . I need your help in solving my problem .

Basically i have an csv file which is in C:\

I need to load the file into excel, ask user for number of groups , devide the data into groups ( into different sheets naming grp1, grp2... in a single excel file) specified by random number generation.

I have reached till below code and not sure how to go further . Need your help !!.

Your help is very much appricated ,

thanks,
sai

Sub GetImportFileName()
MsgBox "This macro loads a csv file and generates an excel file with different random groups"
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
' Set up list of file filters
Finfo = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"""
' Display *.* by default
FilterIndex = 5
' Set the dialog box caption
Title = "Select a File to Import"
' Get the filename
FileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title)
' Handle return info from dialog box
If FileName = False Then
MsgBox "No file was selected."""
Else
MsgBox "You selected " & FileName
End If

Title1 = "Show the path to save the excel file"
OPFileName = Application.GetOpenFilename(Finfo, _
FilterIndex, Title1)

Workbooks.Open FileName:=FileName

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B21")



ActiveWorkbook.SaveAs FileName:=OPFileName, FileFormat:=xlExcel5, CreateBackup:=False

nob = InputBox("Enter the Number of Random Groups")
MsgBox nob & " is the number of random Groups."
Workbooks.Open FileName:=OPFileName
If nob > 0 Then Sheets.Add Count:=nob
Sheets.Add.Name = "Grp"

End Sub


Hi all,

Im using the code below to open a txt file in excel. My problem is that I can select the file but it does not open in my workbook (SI_DIR_Import). Can anyone tell me what Im doing wrong or what needs to be done.


Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String

' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Display full path and name of the file
MsgBox "You selected " & FileName
End Sub



Thanks.


Hello All,

I am able to open a .txt file in a workbook using ths code below but my problem now is that the .txt file is delimited (has commas). Anyone know how i can open the txt file delimited?


Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String

' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Display full path and name of the file
'MsgBox "You selected " & FileName

' Display full path and name of the file
MsgBox "You selected " & FileName
Workbooks.Open FileName
End Sub




I'm using VBA in Excel 2013 to do something that shouldn't be hard. I want the user to be able to choose a file. When I open I dialog box to choose the file, and then try to open the file, I get a Runtime 1004 error "Sorry, we could find <filename>. Is it possible it was moved, renamed, or deleted?" However, when I hard code workbooks.open <filename with path> it opens fine. Here is the code to prompt for the file:

Sub GetImportFileName2(spath, UFileName)
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String
Dim i As Integer
Dim Msg As String
' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Comma Separated Files (*.csv),*.csv," & _
"Excel Files (*.xlsx),*.xlsx," & _
"All Files (*.*),*.*"
' Display *.xlsx by default
FilterIndex = 4
' Set the dialog box caption
Title = "Select a file to open:"
' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)
' Exit if dialog box canceled
If Not IsArray(FileName) Then
MsgBox "No file was selected."
Exit Sub
End If
' Display full path and name of the files
For i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
Next i
spath = Left(Msg, InStrRev(Msg, "\"))
MsgBox "Path: " & spath
UFileName = Mid(Msg, InStrRev(Msg, "\") + 1)
MsgBox "File: " & UFileName
End Sub

As you can see I have inserted a bunch of MsgBox calls so I can check the path and filename. Everything is fine. Then I do:

Workbooks.Open spath & UFileName

And I get the error (the path and filename shown in the error dialog box as not being found is the correct path and filename, so I'm concatenating fine). However, if I just do:

Workbooks.Open FileName:="path and filename exactly as it shows in the error message"

The file opens fine.

Thanks for listening :-)

Hi folks
At work we have excel 2003. Every time when I open some 2007 files with extensions .xlsm or xlsx for a split second it shows a little window and says its converting the file or something and it has cancel option. So we have Excel 2003 and I suppose the machine has the support package to convert the files.
Now I need to have a macro that is capable to open up a 2007 file with extension xlsx and xlsm (also regular xls file as well please) and then carry on with the remaining code (concatenate some columns, index/match lookup from the file where the macro was launched).
At the moment I have the following code which should allow me to open a xlsx and write in some text to header row:

Code:

Option Explicit
 Sub testme()
Dim Finfo As String
 Dim FilterIndex As Integer
 Dim Title As String
 Dim FileName As Variant
 ' Set up list of file filters
 Finfo = "Workbook (*.xls),*.xls," & "Office 2007 Excel Workbook (*.xlsx),*.xlsx,"
 ' Display *.* by default
 FilterIndex = 1
 ' Set the dialog box caption
 Title = "Select a File to Import"
 ' Get the filename
FileName = Application.GetOpenFilename(MultiSelect:=True)
 ' Handle return info from dialog box
 If FileName = False Then
 MsgBox "No file was selected."
 Else
 'MsgBox "You selected " & FileName
 ' opens an the excel file at the location: FileName
 Workbooks.Open (FileName)
 End If
 
 
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "test"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "test"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "test"
 End Sub


If I use this code on regular xls it works. But as soon as I try to use .xlsx it prompts me with error:

"this file is not in recognizable format."

If you know the file is is from another program which is incompatible with Microsoft Office Excel, click Cancel, then open this file in its original application.
If you suspect the file is damaged, click Help for more information about solving the problem.
If you still want to see what text is contained in the file, click OK. Then click Finish in the Text Import Wizard.

When clicking OK it does what it says. Its all text and its all gibberish.


To make things even more harder the file that I would try to open will always have a dynamic name so it can be different name every single occasion. I guess I could change it to something specific if there is no way around it. Some of the files may prompt that it has macros so do you want to enable the macros (Macro security is on medium setting).

Is there any solution to my problem? I have tried bunch of different stuff and also Googled about the subject with no luck.

Any help would be immensely appreciated.
Cheers
Rain


Hello All,

When I use the following code to open a file dialog box, I would like to have the path displayed on a command button:


Code:

Sub CommandButton3_Click()
    
    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer
    
    '   Set Drive letter
        ChDrive "C:\"
    '   Set to Specified Path\Folder
        ChDir "C:\oic\"
    '   Set File Filter
        Filt = "PIW files (*.piw), *.piw"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        FileName = Application.GetOpenFileName(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If FileName = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    '   Open Selected Workbook
        Workbooks.Open FileName

End Sub


Not on this command button obviously, but lets say on one called Show_Filename.

Any ideas out there?

Thanks,

Kurt


I am working on a macro that will open up an array of workbooks and then individually go through each workbook and format the tabs within each workbook. I have gotten as far as being able to select a range of workbooks to open, and also to format. However, the problem is getting the selected group of .xls files to open. Pasting code for help!!

Code:

Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String
Dim i As Integer

'Set up list of file filters
Filt = "Excel Files (*.xls), *.xls"

'Display *.xls by default
FilterIndex = 1

'Set the dialog box caption
Title = "Select Files to Format"

'Get the file name
FileName = Application.GetOpenFilename _
    (FileFilter:=Filt, _
    FilterIndex:=FilterIndex, _
    Title:=Title, _
    MultiSelect:=True)
    
'Exit if dialog box canceled
If Not IsArray(FileName) Then
    MsgBox "No file was selected."
    Exit Sub
End If


'Loop through selected files and format them


For i = 1 To 5
Workbooks.Open(Filename:=FileName)
    ActiveSheet.Select
    Application.Run "PERSONAL.XLS!Tab_Format"
Next i
    
End Sub





Hi Everyone,
Here is one for ya...I would like to create a macro that renames a series of ".out" files in a batch and replace the old files with the renamed files. I am new to programming in general and know very little about VB, can someone PLEASE guide me.

I would like to do this by bring up a prompt to ask the user to select the file that he/she would like to rename. So if the person has saved a file called "Fluid" I would like to have the macro rename the file "Fluid" as "Fluid_Out", so have a predesignated name for the file that is being renamed.. I kinda have an idea, I have attached the code to this page. I think this code should go embedded or integrated with the "Rename File FUnction". Let me know what you guys think please.

Thanks.

Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
'Set up list of file filters
Filt = "All Files (*.*),*.*"
'Display *.* by default
FilterIndex = 1
'Set the dialog box caption
Title = "Select a file to rename and import"
'Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)
'Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If
'Display full path and name of the file
MsgBox "You selected" & FileName
End Sub


Hello All,

Here is some code that Smitty provided me:

Code:

Private Sub CommandButton3_Click()
    '   Set Drive letter
        ChDrive "C:\"
    '   Set to Specified Path\Folder
        ChDir "C:\oic\"
    '   Set File Filter
        Filt = "PIW files (*.piw), *.piw"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        Filename = Application.GetOpenFileName(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If Filename = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & Filename, vbInformation, "Proceed")
    '   Open Selected Workbook
        Workbooks.Open Filename

End Sub


However the code stops on the first Filt and states Variable not defined.

Any and all help is appreciated.

Kurt


I need some help from some of the experts out there. This question is probably easy for you, but I can not seem to get it! I am pulling some data from another file, and the filepath/file needs to be set as a variable(the one the user selects). Below is the code utilized to let the user select the file path and the file.

Code:

Public Sub workOnSheet()
    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer

    '   Set Drive letter
        ChDrive "H:\"
    '   Set to Specified Path\Folder
        ChDir "H:\TESTBUILD"
    '   Set File Filter
        'Filt = "PID files (*.pid), *.pid"
        Filt = "PIW files (*.xls), *.xls"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        FileName = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If FileName = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    '   Open Selected Workbook
        Workbooks.Open FileName
End Sub


All I need now is to take the file path/file and set it as a variable in my code. Is there a way to do this??? There should be.....

Thanks for any assistance you can give me - SEOT


G'day World,

I use the following script to open my file each week. The filename starts YYYYMMDD...xls so it's always the last file on the list. Is there a way to go straight to that last file rather than rely on the Open dialogue box? Thanks in advance, Bruce

Sub GetOpenFileName_Weekly_Current_Claims_Rpt()
'Sets up the file-open box in Excel
' Set Drive letter
ChDrive "K:\"
' Set to Your Directory/Path Folder
ChDir "K:\Business Reporting\Customer Services\Claims\Current Claims\Weekly\2007-2008"
' Set File Filter
Filt = "Excel Files (*.xls), *.xls"
' Set *.* to Default
FilterIndex = 5
' Set Dialogue Box Caption
Title = "Open Current claims report"
' Get FileName
Filename = Application.GetOpenFilename(FileFilter:=Filt, _
FilterIndex:=FilterIndex, Title:=Title)
' Exit if Dialogue box cancelled
If Filename = False Then
'Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
Exit Sub
End If
' Display Full Path & File Name
' Response = MsgBox("You selected " & Filename, vbInformation, "Proceed")
' Open Selected Workbook autofilter
Workbooks.Open Filename
End Sub


Hi I am trying to copy a cell and paste it in the right hand column against each row that is pasted. The following is what I have. At the moment all it does is post in column d and row 1 for both parts rather than against each row that copied. Does anybody know how to get around this?

Thanks

Code:

Sub GetImportFileName()
    Dim Finfo As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As Variant
    Dim strFileName As String
    Dim wb As Workbook

    
    
'   Set up list of file filters
    Finfo = "All Files (*.*),*.*"
    
'   Display *.* by default
    FilterIndex = 5
    
'   Set the dialog box caption
    Title = "Select a file to Import"
    
'   Get the Filename
    FileName = Application.GetOpenFilename(Finfo, FilterIndex, Title)
    
'   Open the Filename
    Workbooks.Open FileName
    Set wb = ActiveWorkbook

Sheets("JLINTERN").Select
strName = Cells(4, 6).Value
Range("FIRST").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Timesheets.xls").Activate
For i = intstartrow To intendrow
    Cells(1, 4).Value = strName
Next i
Sheets("Posting").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
wb.Activate
Sheets("TEVANS").Select
strName = Cells(4, 6).Value
Range("FIRST").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Timesheets.xls").Activate
For i = intstartrow To intendrow
    Cells(1, 4).Value = strName
Next i
Sheets("Posting").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   
  
  
End Sub





Hi All,

Is there a way to make the contents of a particular cell in a worksheet show where to pick up another file. Here is my code to pick up a file:

Code:

Sub workOnSheet()

    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer 
    Dim NameOnly As String
    '   Set Drive letter
        ChDrive "E:\"
    '   Set to Specified Path\Folder
        ChDir "E:\PATH HERE"
    '   Set File Filter
        'Filt = "PID files (*.pid), *.pid"
        Filt = "PIW files (*.xls), *.xls"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        FileName = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If FileName = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            End
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    
        NameOnly = GetFileName(CStr(FileName))
        
        DEFName = NameOnly
        
    '   Open Selected Workbook
        Workbooks.Open FileName


Then, I need to be able to pickup the path and drive from a cell out of a particular worksheet so I user can change it when they move the project around.
Lets say I want to put the drive and path under A1 and A2 in workbook named KittyCat where a user can go in and change it. How would I alter the code above to make it accept this. Thank you for any help you can provide on this!!!


Some nice soul here gave me a nice MACRO that allows the user to select a file to open. I just found out that I now need the MACRO to save the selected file "test.txt" without actually opening the file.

Can somebody please help me with the backside of this MACRO???
This is what I have:


Sub marine()
FilterIndex = 5
Title = "Please select a different File"
FileName = Application.GetOpenFileName(FileFilter:=Filt, _
FilterIndex:=FilterIndex, Title:=Title)
If FileName = False Then
Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
Exit Sub
End If
Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
Workbooks.Open FileName

End Sub


hi everyone,

This is my first post .
So I am working this code where the user will be able to select an access database and input the name of the table in an inputbox and the macro will run and grab certain columns with certain criteria and paste it in excel.

So I am having trouble with defining the database path and table name as variables and using the variables in my Array code. you will see that below that the current table name used is "IFENGP" (in bold), and I would like that to be changed to a variable that is defined by the user using an input box. The current code works, but it doesn't take any of the variables i defined.

any ideas?
Thanks,
Sean

Sheets("q_IFENGP").Select
Range("A:A, B:B, c:c, D:D, e:e, f:f").Clear
Selection.Delete

Dim FileName As Variant
Dim TableName As Variant
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String

' Set up list of file filters
Finfo = "All Files (*.*), *.* "

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Please choose the Data Base Containing the IFENGP Table"

'Get the table name
TableName = InputBox("Whats table name?")



'IFENGP

'Get the filename
FileName = Range("FilePath").Value
'FileName = Application.GetOpenFilename(Finfo, FilterIndex, Title)
'return info from the dialog box

If FileName = False Then
MsgBox "No File Selected"

Else



MsgBox (" " & FileName)



Sheets("q_IFENGP").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=;DefaultDir=;D" _
), Array( _
"river={Driver do Microsoft Access (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransa" _
), Array("ctions=0;Threads=3;UserCommitSync=Yes;")), Destination:=Range("A1") _
)
.CommandText = Array( _
"SELECT IFENGP.CONTRACTNO, IFENGP.IDATE, IFENGP.CV0, IFENGP.FV2, IFENGP.VERSION, IFENGP.STATUS" & _
Chr(13) & "" & Chr(10) & "FROM IFENGP IFENGP" & _
Chr(13) & "" & Chr(10) & "WHERE (((IFENGP.Version)='C')AND((IFENGP.Status)='1'))" & _
Chr(13) & "" & Chr(10) & "ORDER BY IFENGP.CONTRACTNO" _
)
.Name = "IFENGP Query"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

Calculate
Sheets("Instructions").Select
Range("c12").Value = FileName

End With
End If
End Sub


Hi,
I am writing some code to open up a file and use the text import as the files are data files.

i currently have
Code:

   Sub openfileandimport()

   End Sub

    Dim Filter As String, Title As String
    Dim FilterIndex As Integer
    Dim Filename As Variant
    ' File filters
    Filter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
    ' Default Filter to *.*
    FilterIndex = 3
    ' Set Dialog Caption
    Title = "Select a File to Open"
    ' Select Start Drive & Path
    ChDrive ("C")
    ChDir ("C:\")
    With Application
    ' Set File Name to selected File
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
    End With
    ' Exit on Cancel
    If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
    End If
    ' Open File
    Workbooks.Open Filename
 

    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers _
    :=True

   MsgBox Filename, vbInformation, "File Opened"
   
   End Sub


I manually recorded the text import wizard bit and this is the properties i need for the text file. I am just unsure about how to link the open file code up with the text import wizard code.

Thanks in advance


Dim FPath As Variant, FInfo As String
FInfo = "Excel Workbook (*.xlsx), *.xlsx"
FPath = Application.GetSaveAsFilename("Filename", FInfo)
ActiveWorkbook.SaveAs Filename:=FPath, FileFormat:=xlExcel12


The code is throwing up "Run-Time error '1004': This extension can not be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type."

I've tried msgbox on the FPath variable and it looks fine: "C:\...\Filename.xlsx"

Please help!


PennySaver helped me with the code below and it works great. Now I would like the path to point to the directory on our server were all of the photos are stored but don't know how to do that.

The actual path goes like this: My Network Places\Entire Network\Microsoft Windows Network\sbeach\common files\shared files\photos

Private Sub Image6_Click()
Dim ImgPath
Dim Filt As String, Title As String
Dim FilterIndex As Integer

' Set Drive letter
ChDrive "C:\"
' Set to Specified Path\Folder
ChDir "C:\Documents and Settings\All Users\Desktop\"
' Set File Filter
Filt = "Image Files (*.jpg; *.gif), *.jpg; *.gif"
' Set *.* FilterIndex to the number of Filters selected
FilterIndex = 2
' Set Dialogue Box Caption
Title = "Please select a different Image"
' Get FileName
ImgPath = Application.GetOpenFilename(FileFilter:=Filt, _
FilterIndex:=FilterIndex, Title:=Title)
' Exit if Dialogue box cancelled
If ImgPath = False Then
MsgBox "No File was selected", vbOKOnly & vbCritical, "Selection Error"
Exit Sub
End If
' Display Full Path & File Name
MsgBox "You selected " & ImgPath, vbInformation, "Proceed"
' Replace Image
Image6.Picture = LoadPicture(ImgPath)

End Sub

Thanks for any help you can provide!


I am updating a Print Utility workbook. I want this routine to open a selected list of files and run a draw routine from the current file. The problem that I am encountering is that the filepath chages with each file.

Here is what I have so far.

Sub DrawandPrinttoPDF()
Dim Finfo As String
Dim Title As String
Dim FileNames
Dim File
Dim I As Integer

' Speed up macro
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Set up File Filter List
Finfo = "Excel Files (*.xls),*xls,"

' Set up dialog box caption
Title = "Draw and Print to PDF"

FileNames = Application.GetOpenFilename(Finfo, , Title, , MultiSelect:=True)

If IsArray(FileNames) Then

For I = LBound(FileNames) To UBound(FileNames)
File = FileNames(I)
Application.Workbooks.Open File
ChDir " C:\Documents and Settings\amurray\Desktop "
Workbooks.Open Filename:= _
" C:\Documents and Settings\amurray\Desktop\BF-1A_1B.xls "
Application.Run " 'BF-1A_1B.xls' !Draw"
Next I

Else
MsgBox "No files were selected."
End If

End Sub


What is highlighted in red is what I need to change with each file that is selected. The Draw routine is specific for each workbook. I would rather not put all of these draw routines into a master sheet if at all possible.

Thanks in advance for the help.


Hi

I've this code to present the user of a list of files to open Code:

' File filters
    Filter = "Excel Files (*.xls*),*.xls*  "
    FilterIndex = 1
    ' Set DialogCaption
    Title = "Select a File to Open"
 
    ' Select Start Drive & Path
    ChDir "C:\ABC\CBA LOSE"  'You will need to change this to S:\ABC\CBA LOSE\
 
    With Application
        ' Set File Name to selected File
        fileName = .GetOpenFilename(Filter, FilterIndex, Title)
    End With
    ' Exit on Cancel
    If fileName = False Then
        MsgBox "No file was selected."
        Exit Sub
    End If
    ' Open File
    Workbooks.Open fileName


and it presents with this screen screen capture.jpg

I'd like the screen to NOT include any files with "Apple" in the file name. So, the fifth item on the list (Apple MTS (Template).xls) would not be displayed. ( I've tried to filter on "<> InStr(File.Name,"Apple")" with no success.

Any way to accomplish this?


This small snippet of code had worked fine for quite a while. Since the last time I ran it though, I changed to enable Option Explicit to clean up the variables. Since that is the last thing that has changed, it is logical to me that must have affected this code...

I get the dialog box ok, I can select multiple files, and the MsgBox returns a string of the first file selected.

I have explicitly defined the Filter, FilterIndex, Title, and Filename variables as type Variant, as described in the VB Help.
Here's the code stripped to its minimum

With Application
' Set File Name Array to selected Files (allow multiple)
Filename = .GetOpenFileName(, , Title, , True)
MsgBox "Files selected are " & Filename
End With
' Exit on Cancel

' The IsArray() now fails... but used to work

If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If

(continues on)

Thanks for the help.


The top part of the Sub below is written to select multiple files and to create multiple sheets to accommodate the data in these files. One sheet per file. This part is working.

The last loop in the Sub imports the data. Currently the this loop can only import a file named: M:\2.xml.
How do I need to change the code so that it imports all the files I selected?

Code:

Sub ImportXMLFiles()

'Define variables
    
    Dim Filt As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As Variant
    Dim i As Integer
    Dim j As Integer

'Select files to Import

        Filt = "XML Files (*.xml) ,*.xml," & _
                "All Files (*. *), *. *"
        FilterIndex = 5
        Title = "Select a File to Import"
        FileName = Application.GetOpenFilename _
            (FileFilter:=Filt, _
            FilterIndex:=FilterIndex, _
            Title:=Title, _
            MultiSelect:=True)

'Create sheets, one for each file to import
       
    For j = LBound(FileName) To UBound(FileName) - 1
        Sheets.Add After:=Sheets(j)
        Sheets(j + 1).Select
        Sheets(j + 1).Name = j + 1
    Next j

'Import files

   For i = LBound(FileName) To UBound(FileName)
       Sheets(i).Select 
       ActiveWorkbook.XmlImport URL:="M:\2.xml", ImportMap:=Nothing, Overwrite:= _
            True, Destination:=Range("$A$1")
   Next i

End Sub





So far my code will allow me to select text files and convert them into excel workbooks, one text file = one workbook. There may be times when 20 text files are selected and other times 10. The part I'm stuck with is taking all of the workbooks and combining all of the information into one workbook calling it "Master" and then deleting the workbooks not needed. Any help would be appreciated. Thanks in advance! Here is the code so far:
Code:

Application.ScreenUpdating = False
' File filters
Filter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
'   Default filter to *.*
    FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("J")
ChDir ("J:\dell\use\reports\test")
With Application
    ' Set File Name Array to selected Files (allow multiple)
    FileName = .GetOpenFilename(Filter, FilterIndex, Title, , True)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(FileName) Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open Files
For i = LBound(FileName) To UBound(FileName)
    'msg = msg & Filename(i) & vbCrLf ' This can be removed
    Workbooks.Open FileName(i)
    Set wsThis = ActiveSheet
    Set myRange = wsThis.Range("A1:A5000")
    numR = myRange.count
        For r = 1 To numR + 1
            If (wsThis.Cells(r, 2))  "Error" Then
                    Rows(r).Select
                    Selection.Delete Shift:=xlUp
                    r = r - 1
            End If
            If (wsThis.Cells(r + 1, 2)) = "" Then
                    Exit For
          End If
        Next r
        Cells.Select
        Cells.EntireColumn.AutoFit
        Range("A1").Select
        If Cells(1, 1) = "" Then
            ActiveWindow.Close False
        End If
Next i
Application.ScreenUpdating = True
End Sub





I can not figure out why the ChDir command doesn't change to "C:\" as programmed, instead it defaults to (I'm guessing last active folder) on my "D" drive (Which is "D:\Test")?
My goal is to check if "PPG" exists in "C:\MEASURE-6000" , if not use "C:\" as a default.
Code:

Private Sub TextBox13_Enter()
' File filters
Filter = "Text Files (*.ppg),*.ppg," & _
        "All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"
' Select Start Drive & Path
If Dir( "C:\MEASURE-6000\PPG" , vbDirectory) = "" Then
     ChDir "C:\" 
Else
    ChDrive ("C:\MEASURE-6000\PPG")
End If

With Application
    ' Set File Name to selected File
    FileName = .GetOpenFileName(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If FileName = False Then
    MsgBox "No file was selected. Please try again or Cancel program."
    CommandButton1.SetFocus
    Exit Sub
End If
TextBox13.Value = FileName
SaveSetting appname:="GeoMeasure", section:="GM Probe Editor", _
        Key:="Filename", setting:=FileName
'MsgBox Filename    'for testing
End Sub


The macro works, but it doesn't work as planned.




I have very simple code. I just want to open a file. The open file dialog opens, I select my file, but it doesn't actually open. Any ideas?

Sub OpenFileCopySheet()
Dim filename

filename = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx")

End Sub