VBA to check for file type

[Bond00]

This is the code i'm using now and its working perfect, the one thing it doesnt do it check for the file type. the only thing i want it to bring the file name in for is *.xls*
i dont see where to edit the code to have that check. :/

Sub FileHyperlinks()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(ThisWorkbook.path & "\All Items\")
i = 1 'The row is starts on (binary)0 = row 1 and 1 = row 2 etc..
'Selects the page # to enter values to (doesn't matter what the page name is)
Sheet1.Activate
'loops through each file in the directory
For Each objFile In objFolder.Files

    'select cell (I think the ,1 is column A "1")
    Sheet1.Range(Sheet1.Cells(i + 1, 1), Sheet1.Cells(i + 1, 1)).Select
    'create hyperlink in selected cell
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        objFile.path, _
        TextToDisplay:=objFile.Name
    i = i + 1
Next objFile
End Sub

This checks a folder and brings all the file names into column A2 and down. also they are hyper linked to the file. its brining in PDFs and other stuff too though, just want excel type files. xlsx, xlsm etc.

thanks!

 

[Nine Zero]

Re: I need help editing VBA to check for file type

I have something similar where it checks for only files with *.csv*

i use something like this

If LCase(Right(pathText.Value, 3)) = "csv" Then
    Set Data = Workbooks.Open(pathText.Value)
Else
    MsgBox "The file must be a .csv file!"
Exit Sub
End If

 

[Fluff]

Re: I need help editing VBA to check for file type

Try

For Each objFile In objFolder.Files
    If objFile Like "*.xls*" Then
        'select cell (I think the ,1 is column A "1")
        Sheet1.Range(Sheet1.Cells(i + 1, 1), Sheet1.Cells(i + 1, 1)).Select
        'create hyperlink in selected cell
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            objFile.Path, _
            TextToDisplay:=objFile.Name
        i = i + 1
    End If
Next objFile
End Sub

 

[Bond00]

Re: I need help editing VBA to check for file type

thanks guys for posting, this did the trick.

Try

For Each objFile In objFolder.Files
    If objFile Like "*.xls*" Then
        'select cell (I think the ,1 is column A "1")
        Sheet1.Range(Sheet1.Cells(i + 1, 1), Sheet1.Cells(i + 1, 1)).Select
        'create hyperlink in selected cell
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            objFile.Path, _
            TextToDisplay:=objFile.Name
        i = i + 1
    End If
Next objFile
End Sub

Do you know if there is a way to also ignore hidden files?

 

[Fluff]

Re: I need help editing VBA to check for file type

Try

Sub FileHyperlinks()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\All Items\")
i = 1 'The row is starts on (binary)0 = row 1 and 1 = row 2 etc..
'Selects the page # to enter values to (doesn't matter what the page name is)
Sheet1.Activate
'loops through each file in the directory
For Each objFile In objFolder.Files
    If objFile Like "*.xls*" Then
        If Not objFile.attributes = 34 Then
            'select cell (I think the ,1 is column A "1")
            Sheet1.Range(Sheet1.Cells(i + 1, 1), Sheet1.Cells(i + 1, 1)).Select
            'create hyperlink in selected cell
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
                objFile.Path, _
                TextToDisplay:=objFile.Name
            i = i + 1
        End If
    End If
Next objFile
End Sub

 

[Bond00]

Re: I need help editing VBA to check for file type

thanks for the help

 

[Fluff]

Re: I need help editing VBA to check for file type

Your welcome

 

[Bond00]

Re: I need help editing VBA to check for file type

Try

Sub FileHyperlinks()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\All Items\")
i = 1 'The row is starts on (binary)0 = row 1 and 1 = row 2 etc..
'Selects the page # to enter values to (doesn't matter what the page name is)
Sheet1.Activate
'loops through each file in the directory
For Each objFile In objFolder.Files
    If objFile Like "*.xls*" Then
        If Not objFile.attributes = 34 Then
            'select cell (I think the ,1 is column A "1")
            Sheet1.Range(Sheet1.Cells(i + 1, 1), Sheet1.Cells(i + 1, 1)).Select
            'create hyperlink in selected cell
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
                objFile.Path, _
                TextToDisplay:=objFile.Name
            i = i + 1
        End If
    End If
Next objFile
End Sub

How do I make it only output the name of the file before the .xls*?

I had this before which worked, but then sometimes there would be an added number and it would get cut off at 6 characters.
TextToDisplay:=Left(objFile.Name, 6)

I then tried this and it didn't work in vba (had an error because of Find)
TextToDisplay:=Left(objFile.Name, FIND( ".", objFile.Name) - 1)

I just want it to be everything to the left of the .

 

[Fluff]

Re: I need help editing VBA to check for file type

If they are always modern files (ie xlsx, xlsm etc) then try

TextToDisplay:=Left(objFile.Name, len(objfile.name)-5)

Otherwise replace FIND in your example with InStr

 

[Bond00]

Re: I need help editing VBA to check for file type

That worked, thanks a lot!