Excel VBA - Salvando Planilhas - Parte 01 - Versões entre 97-2003 e 2007-2016 - Use VBA SaveAs in Excel 97-2016

Excel VBA - Salvando Planilhas - Parte 01 - Versões entre 97-2003 e 2007-2016 - Use VBA SaveAs in Excel 97-2016

Tome cuidado ao utilizar o velho comando SaveAs sem especificar os seus parâmetros. Nas versões anteriores ao MS Excel 2007, quando não digitávamos o comando SaveAs com os respectivos parâmetros, isso não causava muitos problemas porque o VBE interpretava que essa falta significasse que queríamos gravar a planilha com a extensão XLS, pois, entre as versões 97 até a 2003, estas aceitavam este padrão como um modelo executor de scripts ou código VBA. 

Mas devido aos inúmeros formatos disponibilizados pelo Excel a partir das versões de 2007-2016, esta displicência não será mais aceita. Agora precisaremos informar qual o formato de planilha que desejamos gravar.

Por exemplo, entre o Excel 2007-2016, o código falhará se o ActiveWorkbook não for um arquivo xlsm
ActiveWorkbook.SaveAs "E:\Bernardess.xlsm"

Este código sempre funcionará ActiveWorkbook.SaveAs "E:\Bernardess.xlsm", fileformat:=52 ' 52 = xlOpenXMLWorkbookMacroEnabled = xlsm (nos códigos VBA entre as versões 2007-2016)

Abaixo estão os principais formatos suportados pelas versões Excel entre 2007-2016, Note: Nas versões para o Mac os valores são +1:

51 = xlOpenXMLWorkbook (sem macros entre as versões de 2007-2013, xlsx)

52 = xlOpenXMLWorkbookMacroEnabled (com ou sem macros nas versões entre 2007-2013, xlsm)

50 = xlExcel12 (Planilha Binária Excel entre as versões 2007-2013 com ou sem macros, xlsb)

56 = xlExcel8 (Versões entre 97-2003 no formato do Excel 2007-2013, xls)

brazilsalesforceeffectiveness@gmail.com

       

Salve Planilhas com o VBA - Parte 02 - Da versão 2007 à 2016 - Use VBA SaveAs in Excel 2007-2016

Salve Planilhas com o VBA - Parte 02 - Da versão 2007 à 2016 - Use VBA SaveAs in Excel 2007-2016

Abaixo estão 2 exemplos de código VBA que copiam a ActiveSheet para uma nova pasta de trabalho, salvando-a num formato que corresponda à extensão da planilha pai. O segundo exemplo usa GetSaveAsFilename solicitando um caminho e o nome do arquivo.

Este 1º Exemplo você pode usar nas versões Excel 97-2016, o 2º exemplo pode ser usado nas versões Excel 2000-2016. 

Se você executar o código no Excel 2007-2016 ele tomará como referência o FileFormat da planilha pai, salvando o novo arquivo neste formato. Apenas se a planilha pai for um arquivo xlsm e se não houver nenhum código VBA na nova planilha, é que ela salvará o novo arquivo como xlsx. Se a planilha pai não for um xlsx, xlsm ou xls, em seguida será salva como xlsb. 

Se você sempre salvar num determinado formato que possa substituir esta parte da macro:

           Select Case Sourcewb.FileFormat 
                Caso 51: FileExtStr = ".xlsx": FileFormatNum = 51 
                Caso 52: 
                    Se .HasVBProject Então 
                        FileExtStr = ".xlsm": FileFormatNum = 52 
                    Else 
                        FileExtStr = ".xlsx": FileFormatNum = 51 
                    End If 
                Caso 56 : FileExtStr = ".xls": FileFormatNum = 56 
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50 
                End Select


Use um destes desta lista

• FileExtStr = ".xlsb": FileFormatNum = 50 
• FileExtStr = ".xlsx": FileFormatNum = 51
• FileExtStr = ".xlsm": FileFormatNum = 52

Ou talvez queira salvar como csv, txt ou prn.
(também pode usar estes nas versões 97-2003 do Excel)

• FileExtStr = ".csv": FileFormatNum = 6
• FileExtStr = ".txt": FileFormatNum = -4158
• FileExtStr = ".prn": FileFormatNum = 36

Outros examplos

Sub Copy_ActiveSheet_1()
'Trabalhando com o Excel 97-2016
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2016
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
    End With

    '    'Change all cells in the worksheet to values if you want
    '    With Destwb.Sheets(1).UsedRange
    '        .Cells.Copy
    '        .Cells.PasteSpecial xlPasteValues
    '        .Cells(1).Select
    '    End With
    '    Application.CutCopyMode = False

    'Save the new workbook and close it
    TempFilePath = Application.DefaultFilePath & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "yyyy-mm-dd hh-mm-ss")

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        .Close SaveChanges:=False
    End With

    MsgBox "You can find the new file in " & TempFilePath

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


Sub Copy_ActiveSheet_2()
'Working in Excel 2000-2016
    Dim fname As Variant
    Dim NewWb As Workbook
    Dim FileFormatValue As Long

    'Check the Excel version
    If Val(Application.Version) < 9 Then Exit Sub
    If Val(Application.Version) < 12 Then

        'Only choice in the "Save as type" dropdown is Excel files(xls)
        'because the Excel version is 2000-2003
        fname = Application.GetSaveAsFilename(InitialFileName:="", _
        filefilter:="Excel Files (*.xls), *.xls", _
        Title:="This example copies the ActiveSheet to a new workbook")

        If fname <> False Then
            'Copy the ActiveSheet to new workbook
            ActiveSheet.Copy
            Set NewWb = ActiveWorkbook

            'We use the 2000-2003 format xlWorkbookNormal here to save as xls
            NewWb.SaveAs fname, FileFormat:=-4143, CreateBackup:=False
            NewWb.Close False
            Set NewWb = Nothing

        End If
    Else
        'Give the user the choice to save in 2000-2003 format or in one of the
        'new formats. Use the "Save as type" dropdown to make a choice,Default =
        'Excel Macro Enabled Workbook. You can add or remove formats to/from the list
        
        fname = Application.GetSaveAsFilename(InitialFileName:="", filefilter:= _
            " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
            " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
            " Excel 2000-2003 Workbook (*.xls), *.xls," & _
            " Excel Binary Workbook (*.xlsb), *.xlsb", _
            FilterIndex:=2, Title:="This example copies the ActiveSheet to a new workbook")

        'Find the correct FileFormat that match the choice in the "Save as type" list
        If fname <> False Then
            Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, ".", , 1)))
            Case "xls": FileFormatValue = 56
            Case "xlsx": FileFormatValue = 51
            Case "xlsm": FileFormatValue = 52
            Case "xlsb": FileFormatValue = 50
            Case Else: FileFormatValue = 0
            End Select

            'Now we can create/Save the file with the xlFileFormat parameter
            'value that match the file extension
            If FileFormatValue = 0 Then
                MsgBox "Sorry, unknown file extension"
            Else
                'Copies the ActiveSheet to new workbook
                ActiveSheet.Copy
                Set NewWb = ActiveWorkbook

                'Save the file in the format you choose in the "Save as type" dropdown
                NewWb.SaveAs fname, FileFormat:= _
                             FileFormatValue, CreateBackup:=False
                NewWb.Close False
                Set NewWb = Nothing

            End If
        End If
    End If

End Sub

brazilsalesforceeffectiveness@gmail.com

       

VBA Excel - Função DIR - 01 - Percorrer os arquivos em uma pasta - Loop Through the Files in a Folder

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

No VBA do Microsoft Excel, a função Dir é usada para retornar o primeiro nome do arquivo num diretório especificado, e uma lista dos seus atributos.

O nome do arquivo é retornado como uma String. A função Dir pode ser usada sem os respectivos argumentos para retornar o nome do próximo arquivo, neste mesmo diretório.

O uso mais comum da função Dir é percorrer todos os arquivos de uma pasta, executando uma ação em cada um. Outros usos comuns incluem a verificação da existência destes, saber se um diretório existe, ou procurar um arquivo específico.

Percorrer os arquivos em uma pasta

Sub AllFiles()

Dim MyFolder As String 'Path containing the files for looping

Dim MyFile As String 'Filename obtained by Dir function

Let MyFolder = "C:\ExcelFiles" 'Assign directory to MyFolder variable

Let MyFile = Dir (MyFolder) 'Dir gets the first file of the folder

'Loop through all files until Dir cannot find anymore

Do While MyFile <> ""

    The statements you want to run on each file

   

    Let MyFile = Dir 'Dir gets the next file in the folder

Loop

End Sub

brazilsalesforceeffectiveness@gmail.com

       

VBA Excel - Função DIR - 02 - Listar os arquivos de uma pasta em uma planilha - List the Files from a Folder on a Worksheet

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

No VBA do Microsoft Excel, a função Dir é usada para retornar o primeiro nome do arquivo num diretório especificado, e uma lista dos seus atributos.

O nome do arquivo é retornado como uma String. A função Dir pode ser usada sem os respectivos argumentos para retornar o nome do próximo arquivo, neste mesmo diretório.

O uso mais comum da função Dir é percorrer todos os arquivos de uma pasta, executando uma ação em cada um. Outros usos comuns incluem a verificação da existência destes, saber se um diretório existe, ou procurar um arquivo específico.

Listar os arquivos de uma pasta em uma planilha

Sub ListFiles()

Dim MyDirectory As String 'Folder containing the files

Dim MyFile As String 'The filename to enter on the worksheet

Dim NextRow As Long 'The row for the next filename in list

MyDirectory = "C:\ExcelFiles" 'Assign directory to MyDirectory variable

MyFile = Dir(MyDirectory) 'Dir gets the first file in the folder

'Find the next empty row in the list and store in NextRow variable

NextRow = Application.CountA(Range("A:A")) + 1

Do Until MyFile = ""

      Cells(NextRow, 1).value = MyFile

     

      NextRow = NextRow + 1 'Move to the next row

      MyFile = Dir 'Dir gets the name of next file in the folder

Loop

End Sub

brazilsalesforceeffectiveness@gmail.com

       

VBA Excel - Função DIR - 03 - Verificando a existência de um Arquivo - Check if a Files Exists

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

No VBA do Microsoft Excel, a função Dir é usada para retornar o primeiro nome do arquivo num diretório especificado, e uma lista dos seus atributos.

O nome do arquivo é retornado como uma String. A função Dir pode ser usada sem os respectivos argumentos para retornar o nome do próximo arquivo, neste mesmo diretório.

O uso mais comum da função Dir é percorrer todos os arquivos de uma pasta, executando uma ação em cada um. Outros usos comuns incluem a verificação da existência destes, saber se um diretório existe, ou procurar um arquivo específico.

Verificando a existência de um Arquivo

Sub FileExists()

      Dim TheFolder As string 'Location of the file

      Dim FiletoCheck As String 'Name of the file you want to check

TheFolder = "C:\ExcelFiles" 'Assign directory to TheFolder variable

'Capture the name of file to check for using an input box

FiletoCheck = InputBox("Enter the name of the file you want to look for", "Enter file name")

'If FiletoCheck is an empty string then file not found

If FiletoCheck = "" Then

     

      Msgbox "Oh no, the file does not exist"

Else

      Msgbox "Yes, the file exists."

End If

End Sub

Tags: Excel, VBA, Files, Folder, list, Dir, filedatetime, xls, xlsm, xlsb, arquivos, pasta, check, exists, function, directory, 

Excel VBA - Liste todas as Planilhas na Pasta - List All the Excel Files in a folder

Consultancy in Excel, Access, VBA, Dashboards, Scorecards & PowerPoint Projects

DESENVOLVO APLICAÇÕES VBA

Perguntas, Questões e Esclarecimentos

Baixe o Calendário Compacto para 2014 em Excel

O Excel VBA permite que obtenhamos um lista dos arquivos contidos em uma pasta, inserindo-a na planilha.

Também podemos recuperar algumas informações destes arquivos se necessário. Este código VBA listará os nomes e data da última atualização.

Como isso funciona?

A caixa de diálogo de seleção de pasta é usada para tornar mais fácil para o usuário selecionar o local desejado, retornando os arquivos.

A função Dir será usada para retornar cada nome de arquivo da pasta ou diretório.

Este código retornará listando todos os arquivos Excel contidos na pasta. Especificamos isso ao usar *. XLS na função Dir. O curinga e a extensão podem ser alterados para listar todos os arquivos que desejarmos, ou omitir inteiramente alguns arquivos desta.

O método FileDateTime foi usado para capturar a data de criação ou modificação.

Sub ImportFileList()

Dim MyFolder As String 'Store the folder selected by the using

Dim FiletoList As String 'store the name of the file ready for listing

Dim NextRow As Long 'Store the row to write the filename to

On Error Resume Next

Let Application.ScreenUpdating = False

'Display the folder picker dialog box for user selection of directory

With Application.FileDialog(msoFileDialogFolderPicker)

    Let .Title = "Please select a folder"

    .Show

    Let .AllowMultiSelect = False

    If .SelectedItems.Count = 0 Then

        MsgBox "You did not select a folder"

        Exit Sub

    End If

    Let MyFolder = .SelectedItems(1) & "\"

End With

'Dir finds the first Excel workbook in the folder

Let FiletoList = Dir(MyFolder & "*.xls")

Let Range("A1").Value = "Filename"

Let Range("B1").Value = "Date Last Modified"

Let Range("A1:B1").Font.Bold = True

'Find the next empty row in the list

Let NextRow = Application.CountA(Range("A:A")) + 1

'Do whilst the dir function returns an Excel workbook

Do While FiletoList <> ""

    Let Cells(NextRow, 1).Value = FiletoList 'Write the filename into the next available cell

    Let Cells(NextRow, 2).Value = FileDateTime(MyFolder & FiletoList) 'Write the date the cell was last modified

    Let NextRow = NextRow + 1 'Move to next row

    Let FiletoList = Dir 'Dir returns the next Excel workbook in the folder

Loop

Let Application.ScreenUpdating = True

End Sub

Tags: Excel, VBA, Files, Folder, list, Dir, filedatetime, xls, xlsm, xlsb,