Alexis7xs
Alexis7xs

Reputation: 11

Compile error: Type mismatch (converting 32bit to 64bit VBA coding)

This is my first time using VBA so I need help. This is a macro in Excel to convert a file from one Excel extension to another. I have starting converting this code from 32 bit to work in 64 bit and found online most the key parts to change but now it is giving me Compile Errors: Type mismatch and I have no idea what to change. Any help is appreciated.

The Error shows the yellow arrow at Sub loopConvert() and the cntToConvert as the blue highlighted section. Picture included. Picture of Error

Option Explicit
'Created by David Miller (dlmille on E-E October, 2011)
'Feel free to use and share, please maintain appropriate acknowledgements the author and link where you found this code.
Sub loopConvert()
Dim fPath As String
Dim fName As String, fSaveAsFilePath As String, fOriginalFilePath As String
Dim wBook As Workbook, fFilesToProcess() As String
Dim numconverted As LongPtr, cntToConvert As LongPtr, i As LongPtr
Dim killOnSave As Boolean, xMsg As LongPtr, overWrite As Boolean, pOverWrite As Boolean
Dim silentMode As Boolean
Dim removeMacros As Boolean
Dim fromFormat As String, toformat As String
Dim saveFormat As LongPtr
Dim wkb As Workbook, wks As Worksheet

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Control Panel")

    removeMacros = IIf(wks.CheckBoxes("Check Box 1").Value = 1, True, False)
    silentMode = IIf(wks.CheckBoxes("Check Box 2").Value = 1, True, False)
    killOnSave = IIf(wks.CheckBoxes("Check Box 3").Value = 1, True, False)

    fromFormat = wkb.Names("fromFormat").RefersToRange
    toformat = wkb.Names("toFormat").RefersToRange
    saveFormat = IIf(toformat = ".XLS", xlExcel8, IIf(toformat = ".XLSX", xlOpenXMLWorkbook, xlOpenXMLWorkbookMacroEnabled))

    Application.DisplayAlerts = False 'no user prompting, taking all defaults
    Application.ScreenUpdating = False

    fPath = GetFolderName("Select Folder for " & fromFormat & " to " & toformat & " conversion")

    If fPath = "" Then
        MsgBox "You didn't select a folder", vbCritical, "Aborting!"
        Exit Sub
    Else
        fName = Dir(fPath & "\*" & fromFormat)
        If fName = "" Then
            MsgBox "There aren't any " & fromFormat & " files in the " & fPath & " directory", vbCritical, "Aborting"
            Exit Sub
        Else 'get a file count of files to be processed, then process them in the next step

            Do
                If UCase(Right(fName, Len(fromFormat))) = UCase(fromFormat) Then 'to differentiate between dir *.xls and inadvertently get *.xls???
                    ReDim Preserve fFilesToProcess(cntToConvert) As String
                    fFilesToProcess(cntToConvert) = fName
                    cntToConvert = cntToConvert + 1
                End If

                fName = Dir

            Loop Until fName = ""

            If cntToConvert = 0 Then 'we were looking for .XLS and there was only .XLS??? or nothing, then abort
                MsgBox "There aren't any " & fromFormat & " files in the " & fPath & " directory", vbCritical, "Aborting"
                Exit Sub
            End If

            If Not silentMode Then
                xMsg = MsgBox("There are " & cntToConvert & " " & fromFormat & " files to convert to " & toformat & ".  Do you want to delete the " & fromFormat & " files as they are processed?", vbYesNoCancel, "Select an Option")
                killOnSave = False 'already false, but just a reminder this is in here!

                If xMsg = vbYes Then
                    killOnSave = True
                ElseIf xMsg = vbCancel Then
                    GoTo processComplete
                End If
            Else
                pOverWrite = True
            End If

            Application.EnableEvents = False 'turn off events so macros don't fire on excel file opens

            For i = 0 To cntToConvert - 1 'process each file for conversion, displaying status as progress...

                Application.StatusBar = "Processing: " & i + 1 & " of " & cntToConvert & " file: " & fName

                fName = fFilesToProcess(i)
                'open and convert file
                On Error GoTo errHandler
                fOriginalFilePath = fPath & "\" & fName

                'you could also check to see if the save as file already exists, before you open convert and save on top!
                overWrite = False
                fSaveAsFilePath = fPath & "\" & Mid(fName, 1, Len(fName) - Len(fromFormat)) & toformat
                If Not pOverWrite Then
                    If FileFolderExists(fSaveAsFilePath) Then
                        xMsg = MsgBox("File: " & fSaveAsFilePath & " already exists, overwrite?", vbYesNoCancel, "Hit Yes to Overwrite, No to Skip, Cancel to quit")
                        If xMsg = vbYes Then
                            overWrite = True
                        ElseIf xMsg = vbCancel Then
                            GoTo processComplete
                        End If
                    Else
                        overWrite = True
                    End If
                Else
                    overWrite = pOverWrite
                End If
                If overWrite Then
                    Set wBook = Application.Workbooks.Open(fOriginalFilePath)

                    If removeMacros And (toformat = ".XLS" Or toformat = ".XLSM") And (fromFormat <> ".XLSX") Then
                    'use Remove Macro Helper
                        Call RemoveAllMacros(wBook)
                    End If

                    wBook.SaveAs Filename:=fSaveAsFilePath, FileFormat:=saveFormat
                    wBook.Close savechanges:=False
                    numconverted = numconverted + 1

                    'optionally, you can delete the file you converted from
                    If killOnSave And fromFormat <> toformat Then
                        Kill fOriginalFilePath
                    End If
                End If

            Next i
        End If
    End If

processComplete:
    On Error GoTo 0
    MsgBox "Completed " & numconverted & " " & fromFormat & " to " & toformat & " conversions", vbOKOnly
    Application.EnableEvents = True 'uncomment if doing other conversions where macros are involved in source workbooks
    Application.StatusBar = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False
    Exit Sub

errHandler:
    Application.StatusBar = False
    MsgBox "For some reason, could not open/save the file: " & fPath & "\" & fName, vbCritical, "Aborting!"
    Resume processComplete

End Sub

Upvotes: 0

Views: 998

Answers (2)

Alexis7xs
Alexis7xs

Reputation: 11

Squidx3 helped me figure out that I needed to just change it. It works now Thanks!

From this:

Dim numconverted As LongPtr, cntToConvert As LongPtr, i As LongPtr

To this:

Dim numconverted As LongPtr, cntToConvert As Long, i As Long

Upvotes: 1

Rajivdmo
Rajivdmo

Reputation: 45

GetFolderName isn't a native function. You copied this code from another place? Verify if getFolderName is there.

Upvotes: 0

Related Questions