To Show Both .xls And .xlsx Files While Opening

Sub Test1()
 Dim Finfo As String
 Dim FilterIndex As Integer
 Dim Title As String
 Dim Filename As Variant
 Dim i As Integer
 Dim Msg As String

' Setup lists of file filters
 Finfo = "Comma separated Files (*.csv),*.csv," & "All Files (*.*),*.*"

' Display*.* by default
 FiterIndex = 5

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

' Get the filename
 Filename = Application.GetOpenFilename(Finfo, FilterIndex, 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
 MsgBox "You selected:" & vbCrLf & Msg
 End Sub

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

Code:

Sub BrowseForDirectory()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim Filename As String


' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
       "Lotus Files (*.prn), *.prn, " & _
       "Comma Seperated Files (*.csv),*.csv, " & _
       "ASCII Files (*.asc),*.asc, " & _
       "Excel Files (*.xls),*.xls, " & _
       "All files (*.*),*.*"
' Display Excell Files 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 is cancelled
 If Filename = "False" Then
   MsgBox "No file was selected"
   
   Exit Sub
 End If
 Workbooks.Open Filename:="" & Filename & ""
 
 ' Set the formatted flag since we loaded a new sheet
 Formatted = False
 End Sub

This works great in the USA
when I try to open a file in China it errors 1004 at this point:
Code:

Workbooks.Open Filename:="" & Filename & ""

when I record a macro opening a file in China it opens the file but when running the recorded macro I get the same error. It can't find the file it just recorded opening.

Any ideas?

hai,

i have no idea how to store only the filename in excel. for example, user browse a file in D:\images\image1.jpeg. but it'll store only image1.jpeg in the excel.

Please if anyone knows code to make it possible...Thank You

Code:

Sub cmbBrowseLayout_Click()
    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
    farmLayoutFile.Value = FileName
    
    MsgBox "You selected " & FileName
End Sub

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

I have a macro that opens another excel file to copy some data from, when the other file opens a messagebox appears that says "This workbook contains one or more links that cannot be updated" and there are two options "Continue" or "Edit Links" is there any way for the code to be changed so that it continues without this message box appearing?

Regards

Code:

    CurrentFileName = ActiveWorkbook.Name
    Title = "Select File"
    FileName = Application.GetOpenFilename(Finfo, FilterIndex, Title)
    If FileName = False Then
    MsgBox "No file was selected."
    Exit Sub
    Else
    MsgBox "You Have Selected " & FileName
    End If
    Workbooks.Open FileName, ReadOnly:=1

Hi I am trying to do something simple but not quite sure how to do it. I am using the GetImportFileName and then selecting information of one sheet from that file and copying to another workbook. I need to know how to then get back to the file I have opened to search the next sheet and so on. The problem I have is with this line.
Workbooks(FileName).Activate

Thanks

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

' 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

WPD
End Sub

Sub WPD()
Sheets("JLINTERN").Select
Range("FIRST").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Timesheets.xls").Activate
Sheets("Posting").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Workbooks(FileName).Activate

Sheets("TEVANS").Select
Range("FIRST").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Timesheets.xls").Activate
Sheets("Posting").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues


End Sub

Hi,

I've currently been battling with some code that is designed to save copies of text files that are named in a list to another location.

The code I've written so far asks for the location of where the files are currently stored, where they should be stored and gets the names of the files from a range in a masterlist file.

I'm using the Name / As method at the moment, but it doesn't seem to be doing the business.

I'd be most grateful for any pointers anyone may have on ths and I feel it's tantalisingly close to being there!

I have copied the code below and thank you in advance for any advice.

twills

Code:

Sub SaveTheFiles()

    Dim myRange As Range
    Dim currentStock As Range
    Dim ShortStockName As String
    Dim OriginationFolderString As String
    Dim DestinationFolderString As String
    
'   Parameters for opening the file that contains the masterlist of stocks
    Dim FInfo As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As Variant

On Error GoTo ErrorHandler

'   First, find the current location of the text files
'   to be copied

    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "G:\"
        .Title = "Where are the txt output files currently located?"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            MsgBox .SelectedItems(1)
            OriginationaFolderString = .SelectedItems(1)
        End If
    End With

'   Secondly, find where the copied files are to be saved

    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "G:\"
        .Title = "Where are the files to be saved?"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancelled"
            Exit Sub
        Else
            MsgBox .SelectedItems(1)
            DestinationFolderString = .SelectedItems(1)
        End If
    End With
        
'---------------------------------------------------
'   Get the excel file (called Masterlist) that
'   contains a row with the names of the files to
'   be copied.
'---------------------------------------------------
    
    
'   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 file name
    FileName = Application.GetOpenFilename(FInfo, FilterIndex, Title)
    
'   Handle return info from dialog box
    If FileName = False Then
        MsgBox "No file was selected."
        Exit Sub
    Else
        MsgBox "You selected " & FileName
    End If
    
    'Open up the masterlist that contains the names of the files to be copied
    Workbooks.Open FileName
    
    ' Get the range that contains the names of the files to be copied
    Set myRange = Application.InputBox("Enter the Input Range", Title:="Please enter range", Type:=8)
    
        For Each currentStock In myRange
            ShortStockName = currentStock.Value
            Name OriginationFolderString & "\" & ShortStockName & ".txt" _
                As DestinationFolderString & "\" & ShortStockName & ".txt"
        Next
    
    MsgBox "Finished"
    Exit Sub
    
ErrorHandler:
    MsgBox "There was a problem with " & ShortStockName
    Err.Clear
    Resume Next
    
End Sub

I am trying to refer to a file name I have opened so I can refer back to it later on. I have to use the getopen method as the filename will change regularly. I have tried using the strfilename to store the workbook name which seems to work but it crashes when I try and refer back to it in the wpd section. Does anybody have any ideas.

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

' 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
FileName = strFileName

WPD
End Sub

Sub WPD()
Sheets("JLINTERN").Select
Range("FIRST").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Timesheets.xls").Activate
Sheets("Posting").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Workbooks(strFileName).Activate
Sheets("TEVANS").Select
Range("FIRST").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Timesheets.xls").Activate
Sheets("Posting").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues


End Sub

Hi all,

I've created a form for users to select a file to import data from to the active excel spreadsheet.

All I want it to do is display the selected filename in cell C3 of the worksheet "Home"

Here is the code I have so far:

Code:

Private Sub CommandButton1_Click()

    Dim Filt As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FileName As Variant
    
    Filt = "Excel Files (*.xls),*.xls," & _
           "Text Files (*.txt),*.txt," & _
           "All Files (*.*),*.*"
           
    FilterIndex = 1
    
    Title = "Select a file to link to"
    
    FileName = Application.GetOpenFilename _
               (FileFilter:=Filt, _
                FilterIndex:=FilterIndex, _
                Title:=Title)
 
End Sub

As always, very grateful for any help

Matt

Hi and Thanks in advance!

Ref.: GetOpenFilename 2003, 2007 Files without the use of *.*

I have this code to select a few Files "Sorry I don't see the tags to writ-it properly:

Sub GetImportFileName()
Dim Title As String: Title = "Select the File/s to Import"
Dim Filt As String: Filt = "Excel Files (*.xls),*.xls," & "All Files (*.*),*.*"
Dim Msg As String
Dim FileName As Variant
Dim FilterIndex As Integer: FilterIndex = 1
Dim xFiles As Byte


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

If Not IsArray(FileName) Then Exit Sub
'Display full path and name of the files
For xFiles = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(xFiles) & vbCrLf
Next xFiles
MsgBox "You selected:" & vbCrLf & Msg
End Sub

My problem:

I would like to prompt the user to select ALL the excel Files no matter if is Excel 2003 or 2007.

At present using: Filt = "Excel Files (*.xls),*.xls,"
but I can only get

I am trying to modify my macro for the import of XML files. I want the new macro to open CSV files. I attempted it but encountered a problem with the syntax for the Open Filename procedure.

Just below is the code with the problem. Further down is the whole script.
Please somebody assist in debugging this problem.

Code:

'Import files

        For i = LBound(FileName) To UBound(FileName)
            Sheets(i).Select
            FileName2 = FileName(i)
            ActiveWorkbook.Open FileName:=FileName2
        Next i

Code:

Sub ImportMultipleFiles()

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

'Select files to Import

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

    '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, _
            MultiSelect:=True)

    'Exit if dialog box canceled
        If Not IsArray(FileName) Then Exit Sub

'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
            FileName2 = FileName(i)
            ActiveWorkbook.Open FileName:=FileName2
                
            Cells.Select
            Selection.RowHeight = 12.5
            Selection.ColumnWidth = 15
    
        Next i
    
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

Hey there,

I have an add in that added a control button in the ribbon but i am unsure how to script the desire outcome.

I have a file path which housed multiple data transfer files......ideally i want the button in the ribbon to open the file path then when the user chooses one of the many DTFs in the folder path i want it to open it in that program. Currently it opens the selected DTF in the active worksheet.

Does that make sense? Heres is the current code which is trigger when the user select the button.

Code:

Sub OpenDTFTemplates(control As IRibbonControl)
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 ("G")
ChDir ("G:\MyPath\DTFs")
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
'MsgBox Filename, vbInformation, "File Opened" ' This can be removed
End Sub

Hi all,
I am trying to compile a macro that will allow me to open 2 spreadhseets, lift values from certain cells in both then dod a simple comparison. So far I have got the files opening without any problems with the code below. I need the names of the newly opened spreadsheets in my original file so I have some reference as to which file to copy the single cell values from. Can the new spreadsheets be displayed in a drop down or even the name put into a cell when it is opened?

Sub OpenSingleFile()
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 ("S")
ChDir ("S:\R&D\R&DSAP\Blend Templates\BLENDS")
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
End Sub

Any guidance greatly appreciated.

Regards,
Neil

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,

I can not figure out how to put a file's contents into a formula. Can anyone help me. Here is how I bring the file in.

Code:

    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer

    '   Set Drive letter
        ChDrive "E:\"
    '   Set to Specified Path\Folder
        ChDir "E:\K Department\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")
            End
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    '   Open Selected Workbook
        Workbooks.Open FileName

Then, I want to take the information out of the file I pick and place it into the following formula below.

Code:

    Range("DF34").Select
    ActiveCell.FormulaR1C1 = _
    "=SUMIF([FileName.xls]Sheet1!R1C9:R2000C9,""PersonName"",[FileName.xls]Sheet1!R1C28:R2000C28)"

How do you get the contents out of the variable FileName. Do I use some special symbol like some of the other languages out there infront or around the variable FileName. PLEASE Help- SEOT