CODEWITHSUNDEEP
excelvba
user1040743
user1040743

Reputation: 11

Using VBA in Excel 2010

We have been using VBA code for years with Excel 2003. I have about 70 files that I pull information from and compile it into one spreadsheet. This time, it only recognizes 3 of the 70. I do not get any errors. I noticed that all 3 recognized are the old version ".xls." and all not being recognized are the ".xlsx". The portion of the code that I think is causing the problem is below. Can anyone help?

Public currApp As String
Public i As String
Public recordC As String
Public excelI As Integer
Public intFileHandle As Integer
Public strRETP As String
Public errFile As String

Public Function loopFiles(ByVal sFolder As String, ByVal noI As Integer)

 'This function will loop through all files in the selected folder
 'to make sure that they are all of excel type

Dim FOLDER, files, file, FSO As Object

excelI = noI
'MsgBox excelI
i = 0

'Dim writeFile As Object
'writeFile = My.Computer.FileSystem.WriteAllText("D:\Test\test.txt", "sdgdfgds", False)

Dim cnn As Connection
Set cnn = New ADODB.Connection

currApp = ActiveWorkbook.path
errFile = currApp & "\errorFile.txt"

If emptyFile.FileExists(errFile) Then
    Kill errFile
Else
    'Do Nothing
End If


'cnn.Open "DSN=AUTOLIV"
'cnn.Open "D:\Work\Projects\Autoliv\Tax workshop\Tax Schedules\sox_questionnaire.mdb"
cnn.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & currApp & "\tax_questionnaire.mdb")

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

Set FSO = CreateObject("Scripting.FileSystemObject")

 'Upon each found excel file it will make a call to saveFiles.


If sFolder <> "" Then
    Set FOLDER = FSO.getfolder(sFolder)
    Set files = FOLDER.files
    For Each file In files
        'ONLY WORK WITH EXCEL FILES
        If file.Type = "Microsoft Excel Worksheet" Then
            Workbooks.Open fileName:=file.path

Upvotes: 1

Views: 2364

Answers (2)

frenchie
frenchie

Reputation: 51987

The problem you're having has to do with this line:

If file.Type = "Microsoft Excel Worksheet" Then

Try adding and replacing it with this:

// add these lines just AFTER the line 'For Each file In files'
IsXLFile = False

FilePath = file.path
FilePath2 = Right(FilePath, 5)
FilePath3 = Mid(FilePath2, InStr(1, FilePath2, ".") + 1)

If UCase(Left(FilePath3, 2)) = "XL" Then IsXLFile = True

// replace faulty line with this line
If IsXLFile = True Then

Let me know how it works. Yes, it'd be possible to compress the statements that start with FilePath into one expression but I left it like that for clarity. Vote and accept the answer if good and follow-up if not.

Have a nice day.

Upvotes: 1

phoog
phoog

Reputation: 43046

xlsx is a "macro-free" workbook. To use VBA in the new file format, the file must be saved as an xlsm file.

EDIT: I read the question too hastily. If you want to identify excel files from the FSO object, use file.Type LIKE "Microsoft Excel *" or similar. Or, check the file's extension against ".xls*"

EDIT

The whole concept of identifying the file type by looking at the file name is fundamentally flawed. It's too easily broken by changes to file extensions and/or the "type" texts associated with those descriptions. It's easily broken by, say, an image file named "file.xls". I would just try opening the file with Workbooks.Open and catch the error. I'd probably put this logic in a separate function:

Function OpenWorkbook(strPath As String) As Workbook

    On Error GoTo ErrorLabel

    Set OpenWorkbook = Workbooks.Open(strPath)

ExitLabel:
    Exit Function

ErrorLabel:
    If Err.Number = 1004 Then
        Resume ExitLabel
    Else
        'other error handling code here
        Resume ExitLabel
    End If
End Function

Then you can consume the function like this:

Dim w As Workbook
Set w = OpenWorkbook(file.Path)
If Not (w Is Nothing) Then
    '...

Upvotes: 1

Related Questions