Excel VBA Import CSV into Excel using Workbooks.OpenText Method

This Excel VBA tutorial explains how to import CSV into Excel automatically using Workbooks.OpenText Method. You may select different delimiters such as Tab, semicolon, comma, space.

You may also want to read:

Import Chinese CSV to Excel

Excel VBA convert CSV to Excel

Excel VBA Import CSV into Excel using Workbooks.OpenText Method

In Excel workbook, you can manually import a CSV file into Excel (Data > From Text / CSV). However, you have to select some options in advance such as delimiter. In order to import CSV into Excel automatically, you may use Workbooks.Open Text Method.

Syntax of Workbooks.Open Text Method

Workbooks.OpenText(FileName, Origin , StartRow , DataType , TextQualifier , ConsecutiveDelimiter , Tab , Semicolon , Comma , Space , Other , OtherChar , FieldInfo , TextVisualLayout , DecimalSeparator , ThousandsSeparator , TrailingMinusNumbers , Local)
NameRequired/OptionalData typeDescription
FileNameRequiredStringSpecifies the file name of the text file to be opened and parsed.
OriginOptionalVariantSpecifies the origin of the text file. Can be one of the following xlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. Additionally, this could be an integer representing the code page number of the desired code page. For example, “1256” would specify that the encoding of the source text file is Arabic (Windows). If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.
StartRowOptionalVariantThe row number at which to start parsing text. The default value is 1.
DataTypeOptionalVariantSpecifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.

NameValueDescription
xlDelimited1Default. Indicates that the file is delimited by delimiter characters.
xlFixedWidth2Indicates that the data in the file is arranged in columns of fixed widths.
TextQualifierOptionalVariant
NameValueDescription
xlTextQualifierDoubleQuote1Double quotation mark (“).
xlTextQualifierNone-4142No delimiter.
xlTextQualifierSingleQuote2Single quotation mark (‘).
ConsecutiveDelimiterOptionalVariantTrue to have consecutive delimiters considered one delimiter. The default is False.
TabOptionalVariantTrue to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.
SemicolonOptionalVariantTrue to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.
CommaOptionalVariantTrue to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.
SpaceOptionalVariantTrue to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.
OtherOptionalVariantTrue to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.
OtherCharOptionalVariant(required if Other is True). Specifies the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.
FieldInfoOptionalVariantAn array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

NameValueDescription
xlDMYFormat4DMY date format.
xlDYMFormat7DYM date format.
xlEMDFormat10EMD date format.
xlGeneralFormat1General.
xlMDYFormat3MDY date format.
xlMYDFormat6MYD date format.
xlSkipColumn9Column is not parsed.
xlTextFormat2Text.
xlYDMFormat8YDM date format.
xlYMDFormat5YMD date format.
TextVisualLayoutOptionalVariantThe visual layout of the text.
DecimalSeparatorOptionalVariantThe decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.
ThousandsSeparatorOptionalVariantThe thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.
TrailingMinusNumbersOptionalVariantSpecify True if numbers with a minus character at the end should be treated as negative numbers. If False or omitted, numbers with a minus character at the end are treated as text.
LocalOptionalVariantSpecify True if regional settings of the machine should be used for separators, numbers and data formatting.

 

Example – Import CSV into Excel using Workbooks.OpenText Method

Suppose we have a staff list as below in csv file, in which the delimiter is comma with double quotation around text that contains comma (job title). Uur goal is import CSV into Excel and delimit the data automatically.

In the VBA code, for the case of a mix of double quotation and no double  quotation, we can skip the TextQualifier argument. We only have to identify the file path and delimiter as below.

Public Sub OpenCsvFile()
  .OpenText Filename:="C:\Users\WYMAN\Desktop\staff list.csv", DataType:=xlDelimited, comma:=True
 End Sub

Create a new workbook, press ALT+F11 to insert the above procedure and then execute the procedure. The CSV file will open in Excel and the data is delimited properly.

 

Note that OpenText Method only opens the CSV in Excel but it is not importing the data into the current workbook.

To do so, we can add some codes to copy the worksheet over to the current workboook .

Public Sub OpenCsvFile()
    Application.ScreenUpdating = False  
    Workbooks.OpenText Filename:="C:\Users\WYMAN\Desktop\staff list.csv", DataType:=xlDelimited, comma:=True 
    With ActiveWorkbook
        .ActiveSheet.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
        .Close
    End With   
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

 

Execute the above procedure, now the delimited csv is added to the current workbook in a new worksheet.

 

Outbound References

https://docs.microsoft.com/zh-tw/office/vba/api/Excel.Workbooks.OpenText

Excel VBA Range PasteSpecial Method

This Excel VBA tutorial explains how to use Range.PasteSpecial Method to paste special such as paste values.

Excel VBA Range PasteSpecial Method

In Excel worksheet, if you copy a Cell and then Paste Special, you can see a list of options. The most commonly used Paste Special is Paste Values, in order to remove all the formula.

In Excel VBA, Paste Speical is done through Range.PasteSpecial Method.

Syntax of Range.PasteSpecial Method

Range.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
NameRequired/OptionalDescription
PasteOptional
NameDescription
xlPasteAllEverything will be pasted.
xlPasteAllExceptBordersEverything except borders will be pasted.
xlPasteAllMergingConditionalFormatsEverything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceThemeEverything will be pasted using the source theme.
xlPasteColumnWidthsCopied column width is pasted.
xlPasteCommentsComments are pasted.
xlPasteFormatsCopied source format is pasted.
xlPasteFormulasFormulas are pasted.
xlPasteFormulasAndNumberFormatsFormulas and Number formats are pasted.
xlPasteValidationValidations are pasted.
xlPasteValuesValues are pasted.
xlPasteValuesAndNumberFormatsValues and Number formats are pasted.
OperationOptional
NameDescription
xlPasteSpecialOperationAddCopied data will be added with the value in the destination cell.
xlPasteSpecialOperationDivideCopied data will be divided with the value in the destination cell.
xlPasteSpecialOperationMultiplyCopied data will be multiplied with the value in the destination cell.
xlPasteSpecialOperationNoneNo calculation will be done in the paste operation.
xlPasteSpecialOperationSubtractCopied data will be subtracted with the value in the destination cell.
SkipBlanksOptionalTrue to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.
TransposeOptionalTrue to transpose rows and columns when the range is pasted.The default value is False.

Example 1 – Paste Value

The below VBA copy the whole active worksheet and paste as value.

Public Sub pasteVal()
    ActiveSheet.Cells.Copy
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
End Sub

Example 2 – Multiply

Suppose we want to multiply Range A1:C2 by 2.

Public Sub pasteMultiply()
    Range("E1").Copy  'Suppose E1 contains value 2
    Range("A1:C2").PasteSpecial Operation:=xlPasteSpecialOperationMultiply
End Sub

Result

 

Copy and Paste in one line

Incidentally, if you just want to copy and paste normally, you can use Copy Method with Destination argument.

The below example copy Range A1:C2 and paste in A10.

Public Sub cpy()
    Range("A1:C2").Copy Destination:=Range("A10")
End Sub

 

Excel VBA Worksheet.Select Method to select worksheets

This Excel VBA tutorial explains how to use Worksheet.Select Method to select a single worksheet or multiple worksheets.

Select worksheets in Excel

When you click on a worksheet tab, the worksheet is highlighted.

To select multiple worksheets, you can hold down Ctrl and then left click the mouse on each worksheet tab.

Excel Assign Page Number 01

To select all worksheets at once, right click on one of the sheet, and then click on Select All Sheets

Excel select multiple worksheets 01

One practical use of selecting multiple worksheets is to print selected worksheets.

In this tutorial, I will explain how to perform the same tasks in the above scenarios using Excel VBA Worksheet.Select Method.

Excel VBA Worksheet.Select Method

In Excel VBA, it is not necessary to select worksheets in order to run a Macro on selected worksheets, because you can use VBA to loop through worksheets with specific name.

Syntax of Worksheet.Select Method

Worksheet.Select(Replace)
NameRequired/OptionalData TypeDescription
ReplaceOptionalVariant(used only with sheets). True to replace the current selection with the specified object. False to extend the current selection to include any previously selected objects and the specified object.

Example 1 – Select a single worksheet

To select Sheet1 only

Sheets("Sheet1").Select

Example 2 – Select multiple worksheets

To select Sheet1 and Sheet2, use the False Property in Sheet2

you can also add the False argument for the first Worksheet

Sheets("Sheet1").Select False
Sheets("Sheet2").Select False

Excel VBA Worksheet.Select Method to select multiple worksheets 01

Example 3 – Select all worksheets in the workbook

The below example selects all worksheets in current workbook

Public Sub selectAllWS()
   For Each ws In ThisWorkbook.Sheets
      ws.Select flase
   Next
End Sub

Excel VBA Worksheet.Select Method to select worksheets 02

After you have selected all worksheets, you can deselect them by selecting anyone of the worksheet. To avoid specifying which worksheet, I use ActiveSheet in the below example.

In multiple selection, ActiveSheet refers to the first selected worksheet.

Public Sub deselectWS()
   ActiveSheet.Select
End Sub

You can also select multiple worksheets using Array.

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff194988.aspx

 

 

Excel VBA Workbooks.Open Method

This Excel VBA tutorial explains how to use Workbooks.Open Method to open another workbook.

Excel VBA Workbooks.Open Method

Workbooks.Open Method is useful when you try to open another Workbook using VBA. For example, you can open a workbook > format the spreadsheet > close the workbook automatically. There are a lot of rarely used arguments for Workbooks.Open Method. As most of them are self explanatory, I will demonstrate some common uses of the Method.

Syntax of Workbooks.Open Method

expression .Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
NameRequired/OptionalData TypeDescription
FileNameOptionalVariantString. The file name of the workbook to be opened.
UpdateLinksOptionalVariantSpecifies the way external references (links) in the file, such as the reference to a range in the Budget.xls workbook in the following formula =SUM([Budget.xls]Annual!C10:C25), are updated. If this argument is omitted, the user is prompted to specify how links will be updated. For more information about the values used by this parameter, see the Remarks section. If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 0, no charts are created; otherwise Microsoft Excel generates charts from the graphs attached to the file.
ReadOnlyOptionalVariantTrue to open the workbook in read-only mode.
FormatOptionalVariantIf Microsoft Excel opens a text file, this argument specifies the delimiter character. If this argument is omitted, the current delimiter is used. For more information about the values used by this parameter, see the Remarks section.
PasswordOptionalVariantA string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.
WriteResPasswordOptionalVariantA string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.
IgnoreReadOnlyRecommendedOptionalVariantTrue to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).
OriginOptionalVariantIf the file is a text file, this argument indicates where it originated, so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly. Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.
DelimiterOptionalVariantIf the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use “,” for commas, use “;” for semicolons, or use a custom character. Only the first character of the string is used.
EditableOptionalVariantIf the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it is a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option does not apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.
NotifyOptionalVariantIf the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.
ConverterOptionalVariantThe index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter does not recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.
AddToMruOptionalVariantTrue to add this workbook to the list of recently used files. The default value is False.
LocalOptionalVariantTrue saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically United States English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
CorruptLoadOptionalXlCorruptLoad Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The default behavior if no value is specified is xlNormalLoad and does not attempt recovery when initiated through the OM.

Example 1 – Open a workbook

The below code opens workbook 1.xls and set the workbook as “wb” so that you can use wb to access other workbook Methods.

Public Sub openWB()
    Set wb = Workbooks.Open("C:\Users\WYMAN\Desktop\excelfolder\1.xls")
End Sub

Example 2 – Open a workbook with password

There are two kinds of password protection – password to open the workbook, and password to modify the workbook.

Excel VBA Workbooks.Open Method 01

In either case, if a workbook is password protected, opening the file will pop up a password box.

You can add the password argument to open the file with password automatically. If you open a non-password protected workbook but you add the password argument, you can still open the workbook.

Lets say you have a workbook 3.xlsx which is non-password protected, using the below code will still be able to open the workbook.

Public Sub openWBpw()
    Set wb = Workbooks.Open(Filename:="C:\Users\WYMAN\Desktop\excelfolder\3.xlsx", Password:="yourpassword")
End Sub

Example 3 – Open a workbook without alert

There are many kinds of alerts that may pop up when you open a workbook, preventing you from running the subsequent procedures. Turn off all alerts ensures you to run all the procedures successfully.

Public Sub openWB2()
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
    Set wb = Workbooks.Open(Filename:="C:\Users\WYMAN\Desktop\excelfolder\3.xlsx", Password:="yourpassword")
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

Other examples

Add password to all Excel workbook in folder

Excel VBA refresh closed workbook

Use Excel Workbooks Open Method to check if workbook open

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff194819.aspx

Excel protect workbook with password

This Excel tutorial explains how to protect workbook with password with VBA and without VBA.

Different kinds of workbook protection

There are several kinds of protection in Excel.

  1. Add password to workbook. Users are asked to open the Excel file with password.
  2. Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
  3. Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
  4. Protect worksheet (cells) with password. Restrict users to edit the cell

Excel protect workbook with password

Navigate to REVIEW > Protect Workbook

excel protect workbook with password 01

 

Check the Structure box or Windows box as appropriate.

Note that in Excel 2013, the Windows box is disabled. I am not sure if there is a way to enable it but even VBA doesn’t work.

excel protect workbook with password 02

Check the boxPrevent users from
Structure– Viewing worksheets you’ve hidden.
– Moving, deleting, hiding, or changing the names of worksheets.
– Inserting new worksheets or chart sheets.
– Moving or copying worksheets to another workbook.
– In PivotTable reports, displaying the source data for a cell in the data area, or displaying page field pages on separate worksheets.
– For scenarios, creating a scenario summary report.
– In the Analysis ToolPak, using the analysis tools that place results on a new worksheet.
Windows– Changing the size and position of the windows for the workbook when the workbook is opened.
– Moving, resizing, or closing the windows.

 

Right click on a worksheet, you will find many options are disabled.

excel protect workbook with password 03

 

Excel protect workbook with password (VBA)

Syntax of Workbook.Protect Method

expression .Protect(Password, Structure, Windows)
NameRequired/OptionalData TypeDescription
PasswordOptionalVariantA string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don’t mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords. It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.
StructureOptionalVariantTrue to protect the structure of the workbook (the relative position of the sheets). The default value is False.
WindowsOptionalVariantTrue to protect the workbook windows. If this argument is omitted, the windows aren’t protected.

Example of Workbook.Protect Method

To protect Workbook structure

ActiveWorkbook.Protect Structure:=True, Windows:=False

Syntax of Workbook.Unprotect

expression .Unprotect(Password)
NameRequired/OptionalData TypeDescription
PasswordOptionalVariantA string that denotes the case-sensitive password to use to unprotect the sheet or workbook. If the sheet or workbook isn’t protected with a password, this argument is ignored. If you omit this argument for a sheet that’s protected with a password, you’ll be prompted for the password. If you omit this argument for a workbook that’s protected with a password, the method fails.

Example of Workbook.Unprotect

ActiveWorkbook.Unprotect

 

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff193800.aspx

 

 

Excel VBA Workbook.SaveAs Method

This Excel VBA tutorial explains how to use Workbook.SaveAs Method.

You may also want to read:

Excel VBA convert CSV to Excel

Excel VBA Workbook.SaveAs Method

VBA Workbook.SaveAs Method is same as the action Save As

Excel add password to workbook 01

 

The options under Tools are also available in VBA Workbook.SaveAs Method.

Excel add password to workbook 02

 

For example, we can add password to workbook.

Excel add password to workbook 03

To understand what Workbook.SaveAs Method is capable of, let’s see what arguments it has.

Syntax – Excel VBA Workbook.SaveAs Method

expression .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
NameRequired/OptionalData TypeDescription
FilenameOptionalVariantA string that indicates the name of the file to be saved. You can include a full path; if you don’t, Microsoft Excel saves the file in the current folder.
FileFormatOptionalVariantThe file format to use when you save the file. For a list of valid choices, see the XlFileFormat enumeration. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

NameValueDescription
xlAddIn18Microsoft Excel 97-2003 Add-In
xlAddIn818Microsoft Excel 97-2003 Add-In
xlCSV6CSV
xlCSVMac22Macintosh CSV
xlCSVMSDOS24MSDOS CSV
xlCSVWindows23Windows CSV
xlCurrentPlatformText-4158Current Platform Text
xlDBF27DBF2
xlDBF38DBF3
xlDBF411DBF4
xlDIF9DIF
xlExcel1250Excel12
xlExcel216Excel2
xlExcel2FarEast27Excel2 FarEast
xlExcel329Excel3
xlExcel433Excel4
xlExcel4Workbook35Excel4 Workbook
xlExcel539Excel5
xlExcel739Excel7
xlExcel856Excel8
xlExcel979543Excel9795
xlHtml44HTML format
xlIntlAddIn26International Add-In
xlIntlMacro25International Macro
xlOpenDocumentSpreadsheet60OpenDocument Spreadsheet
xlOpenXMLAddIn55Open XML Add-In
xlOpenXMLStrictWorkbook61 (&H3D)Strict Open XML file
xlOpenXMLTemplate54Open XML Template
xlOpenXMLTemplateMacroEnabled53Open XML Template Macro Enabled
xlOpenXMLWorkbook51Open XML Workbook
xlOpenXMLWorkbookMacroEnabled52Open XML Workbook Macro Enabled
xlSYLK2SYLK
xlTemplate17Template
xlTemplate817Template 8
xlTextMac19Macintosh Text
xlTextMSDOS21MSDOS Text
xlTextPrinter36Printer Text
xlTextWindows20Windows Text
xlUnicodeText42Unicode Text
xlWebArchive45Web Archive
xlWJ2WD114WJ2WD1
xlWJ340WJ3
xlWJ3FJ341WJ3FJ3
xlWK15WK1
xlWK1ALL31WK1ALL
xlWK1FMT30WK1FMT
xlWK315WK3
xlWK3FM332WK3FM3
xlWK438WK4
xlWKS4Worksheet
xlWorkbookDefault51Workbook default
xlWorkbookNormal-4143Workbook normal
xlWorks2FarEast28Works2 FarEast
xlWQ134WQ1
xlXMLSpreadsheet46XML Spreadsheet
PasswordOptionalVariantA case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPasswordOptionalVariantA string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn’t supplied when the file is opened, the file is opened as read-only.
ReadOnlyRecommendedOptionalVariantTrue to display a message when the file is opened, recommending that the file be opened as read-only.
CreateBackupOptionalVariantTrue to create a backup file.
AccessModeOptionalXlSaveAsAccessModeThe access mode for the workbook.

NameValueDescription
xlExclusive3Exclusive mode
xlNoChange1Default (does not change the access mode)
xlShared2Share list
ConflictResolutionOptionalXlSaveConflictResolutionAn XlSaveConflictResolution value that determines how the method resolves a conflict while saving the workbook. If set to xlUserResolution, the conflict-resolution dialog box is displayed. If set to xlLocalSessionChanges, the local user’s changes are automatically accepted. If set to xlOtherSessionChanges, the changes from other sessions are automatically accepted instead of the local user’s changes. If this argument is omitted, the conflict-resolution dialog box is displayed.

NameValueDescription
xlLocalSessionChanges2The local user’s changes are always accepted.
xlOtherSessionChanges3The local user’s changes are always rejected.
xlUserResolution1A dialog box asks the user to resolve the conflict.
AddToMruOptionalVariantTrue to add this workbook to the list of recently used files. The default value is False.
TextCodepageOptionalVariantIgnored for all languages in Microsoft Excel.

When Excel saves a workbook to one of the CSV or text formats, which are specified by using the FileFormat parameter, it uses the code page that corresponds to the language for the system locale in use on the current computer. This system setting is available in the Control Panel, by clicking Region and Language, clicking the Location tab, under Current location.

TextVisualLayoutOptionalVariantIgnored for all languages in Microsoft Excel.

When Excel saves a workbook to one of the CSV or text formats, which are specified by using the FileFormat parameter, it saves these formats in logical layout. If left-to-right (LTR) text is embedded within right-to-left (RTL) text in the file, or vice versa, logical layout saves the contents of the file in the correct reading order for all languages in the file without regard to direction. When an application opens the file, each run of LTR or RTL characters are rendered in the correct direction according to the character value ranges within the code page. (Unless an application that is designed to display the exact memory layout of the file, such as a debugger or editor, is used to open the file.)

LocalOptionalVariantTrue saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

Example – Excel VBA Workbook.SaveAs Method

When you save as a workbook in a location where the workbook name already exists, you will receive the following prompt box.

Excel protect workbook 10

In order for the Macro to continue to run without prompting the box, the first thing to do is to temporarily turn the alert box off using Application.DisplayAlerts = False, and all answers will become Yes.

The second thing is the Filname argument. Although the documentation says if you omit the Filename, the current folder will be used. I tried with Excel 2013 but it saves to My Document folder, so I use Application.ActiveWorkbook.FullName to make sure the file really saves to the current folder.

The third thing is FileFormat. I choose xlOpenXMLWorkbookMacroEnabled (xlsm) to ensure the Macro is saved successfully.

The below procedure uses SaveAs Method to add password (yourpassword) and then overwrite the existing xlsm workbook.

Sub add_password()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="yourpassword"
'OR using specific path
    'ActiveWorkbook.SaveAs "C:\folderA\test.xlsx", Password:="yourpassword"
    Application.DisplayAlerts = True End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

 

 

Excel VBA clear cell contents with ClearContents Method

This Excel tutorial explains how to clear cell contents / clear cell formats using Clear, ClearContents, ClearFormats Method.

Excel VBA clear cell contents

There are several kinds of “clear” related Excel VBA Methods for different scenarios. I will also explain how to clear single cell contents, clear merged cells contents, and clear blank cells.

Clear single cell contents with ClearContents

There are two ways to clear single cell contents (clear value in a Cell but not formatting)

Assume that you want to clear contents in cell A1, then do one of the followings

Method 1:   Range("A1").value = ""
Method 2:   Range("A1").ClearContents

No matter which method is used, the result will return TRUE if you use IsEmpty() Function on the cell.

Clear merged cell contents

For merged cells, you may not use ClearContents directly on the Cell object as you do on a single cell, otherwise you will receive an error message.

Run -time error ‘1004’: We can’t do that to a merged cell

To clear merged cell, first you need to understand how merged cells behave.

Merged cell such as A1:C1, the value always lies in the cell on the top left (A1). If you run a code such as B1.value = “something”, nothing will change in the spreedsheet, you will not find the value even if you use msgbox to show B1.value. The value in the merged cell is changed only if you type A1.value = “something”.

To clear merged cell data, there are two ways to do that. Take A1:C1 as an example.

Method 1

Change the top left cell (A1) value. Note that changing the value of B1 and C1 will not affect the merged cell value.

A1.value = ""

Method 2

Set a Range (any single range within the merged cell: A1/B1/C1) as variable and then use ClearContents Method.

Set Rng = Range("A1")
Rng.MergeArea.ClearContents

Clear empty cell

Sometimes you may find that a cell looks empty, but when you use COUNTA function on that cell, you count something, meaning that the cell is technically not empty. One major reason is that the data source was downloaded from other systems and export as Excel.

excel clear cell contents 01

In order to make those fake empty cells truly empty, you can use the below procedure, which handles both single cells and merged cells.

Public Sub clear_empty_cell()
    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
End Sub

After “data cleansing”, you may want to remove blank rows from worksheet, click here to read more.

Other clear Methods

There are several more clear Methods which are self explanatory.

ClearRemove formatting and contents
ClearFormatsRemove cell formatting, including font format
ClearCommentsRemove comments
ClearHyperlinksReomve hyperlinks, but the default format of hyperlink is not removed
Clear NotesRemove Notes
ClearOutlineRemove Outline

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff835589.aspx

 

Excel VBA LinkInfo Method

This Excel VBA tutorial explains VBA LinkInfo Method to check status of linked workbook.

You may also want to read:

Excel find all external links and broken links in workbook

Excel VBA LinkInfo Method

Workbook LinkInfo Method is usually used to check status of linked workbook, so that we know whether the workbook link is broken or normal.

Usually this Method is used with LinkSources Method.

Syntax of LinkInfo

Workbook.LinkInfo(Name, LinkInfo, Type, EditionRef)
NameRequired/OptionalData TypeDescription
NameRequiredStringThe name of the link.
LinkInfoRequiredXlLinkInfoThe type of information to be returned.

NameValueDescription
xlEditionDate2Applies only to editions in the Macintosh operating system.
xlLinkInfoStatus3Returns the link status.
xlUpdateState1Specifies whether the link updates automatically or manually.
TypeOptionalVariantOne of the constants of XlLinkInfoType specifying the type of link to return.

NameValueDescription
xlLinkInfoOLELinks2OLE or DDE server
xlLinkInfoPublishers5Publisher
xlLinkInfoSubscribers6Subscriber
EditionRefOptionalVariantIf the link is an edition, this argument specifies the edition reference as a string in R1C1 style. This argument is required if there’s more than one publisher or subscriber with the same name in the workbook.

If you choose to return xlLinkInfoStatus, a value from 0 to 10 will return (or you can use XlLinkInfoStatus to represent the number)

ValueXlLinkInfoStatusDescription
0xlLinkStatusOKNo errors.
1xlLinkStatusMissingFileFile missing.
2xlLinkStatusMissingSheetSheet missing.
3xlLinkStatusOldStatus may be out of date.
4xlLinkStatusSourceNotCalculatedNot yet calculated.
5xlLinkStatusIndeterminateUnable to determine status.
6xlLinkStatusNotStartedNot started.
7xlLinkStatusInvalidNameInvalid name.
8xlLinkStatusSourceNotOpenNot open.
9xlLinkStatusSourceOpenSource document is open.
10xlLinkStatusCopiedValuesCopied values.

Example of LinkInfo

Assume that the active workbook contains vlookup formula to lookup another workbook called “workbook2” and it is currently open.

Run the below code

Public Sub test()
    MsgBox (ActiveWorkbook.LinkInfo("workbook2.xls", xlLinkInfoStatus))
End Sub

A message box will pop up and return number “9” (xlLinkStatusSourceOpen)

Convert xlLinkInfoStatus to Description

The returned xlLinkInfoStatus is a number, it is better to convert it to a text description.

Public Function linkStatusDescr(statusCode)
           Select Case statusCode
                Case xlLinkStatusCopiedValues
                    linkStatusDescr = "Copied values"
                Case xlLinkStatusIndeterminate
                    linkStatusDescr = "Unable to determine status"
                Case xlLinkStatusInvalidName
                    linkStatusDescr = "Invalid name"
                Case xlLinkStatusMissingFile
                    linkStatusDescr = "File missing"
                Case xlLinkStatusMissingSheet
                    linkStatusDescr = "Sheet missing"
                Case xlLinkStatusNotStarted
                    linkStatusDescr = "Not started"
                Case xlLinkStatusOK
                    linkStatusDescr = "No errors"
                Case xlLinkStatusOld
                    linkStatusDescr = "Status may be out of date"
                Case xlLinkStatusSourceNotCalculated
                    linkStatusDescr = "Source not calculated yet"
                Case xlLinkStatusSourceNotOpen
                    linkStatusDescr = "Source not open"
                Case xlLinkStatusSourceOpen
                    linkStatusDescr = "Source open"
                Case Else
                    linkStatusDescr = "Unknown status"
            End Select
End Function

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff192971.aspx

 

Excel VBA protect worksheet with password

This Excel VBA tutorial explains how to use VBA to protect worksheet with password.

You may also want to read:

Excel VBA hide worksheet with password (xlVeryHidden)

Excel VBA Add password to Excel workbook in folder

Protect VBA code with password

Excel VBA copy contents of protected worksheet

Different kinds of workbook protection

There are several kinds of protection in Excel.

  1. Add password to workbook. Users are asked to open the Excel file with password.
  2. Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
  3. Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
  4. Protect worksheet (cells) with password. Restrict users to edit the cell

Lock Cell and Unlock Cell

Before doing any worksheet Protection, the first thing to do is to ensure the Cell you want to protect is locked.

Right click on a Cell > Format Cells > Protection > check Locked

protect_worksheet_01

To lock a Cell in VBA

Range("A1").Locked = TRUE

To unlock a Cell in VBA

Range("A1").Locked = FALSE

Similarly, Hidden can also be enabled in VBA

Range("A1").FormulaHidden = TRUE

Protect worksheet

Now the next step is to do the actual protection using Worksheet.Protect Method.

Syntax of Worksheet.Protect

Worksheet.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
NameRequired/OptionalDescription
PasswordOptionalA string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook.
DrawingObjectsOptionalTrue to protect shapes. The default value is True.
ContentsOptionalTrue to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the locked cells. The default value is True.
ScenariosOptionalTrue to protect scenarios. This argument is valid only for worksheets. The default value is True.
UserInterfaceOnlyOptionalTrue to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.
AllowFormattingCellsOptionalTrue allows the user to format any cell on a protected worksheet. The default value is False.
AllowFormattingColumnsOptionalTrue allows the user to format any column on a protected worksheet. The default value is False.
AllowFormattingRowsOptionalTrue allows the user to format any row on a protected. The default value is False.
AllowInsertingColumnsOptionalTrue allows the user to insert columns on the protected worksheet. The default value is False.
AllowInsertingRowsOptionalTrue allows the user to insert rows on the protected worksheet. The default value is False.
AllowInsertingHyperlinksOptionalTrue allows the user to insert hyperlinks on the worksheet. The default value is False.
AllowDeletingColumnsOptionalTrue allows the user to delete columns on the protected worksheet, where every cell in the column to be deleted is unlocked. The default value is False.
AllowDeletingRowsOptionalTrue allows the user to delete rows on the protected worksheet, where every cell in the row to be deleted is unlocked. The default value is False.
AllowSortingOptionalTrue allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False.
AllowFilteringOptionalTrue allows the user to set filters on the protected worksheet. Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter. The default value is False.
AllowUsingPivotTablesOptionalTrue allows the user to use pivot table reports on the protected worksheet. The default value is False.

The simplest way to set password is to leave everything default and just set the password.

Sub proct()
    ActiveSheet.Protect (Password = "abc123")
End Sub

To protect all worksheets in workbook

Sub proctAll()
    For Each sht In ActiveWorkbook.Sheets
       sht.Protect (Password = "abc123")
    Next
End Sub

Since everyone can press ALT+F11 to see your password, you should lock the project view of VBE.

Press ALT+F11 > Navigate to Tools > VBAProject Properties > Protection > check the box “Lock project for viewing” > enter password (close the workbook and reopen to take effect)

hide_worksheet_1

Unprotect worksheet

Unprotect is more simple, unprotect Method has only one optional argument – the password.

Sub unproct()
    ActiveSheet.Unprotect (Password = "abc123")
End Sub

If the sheet or workbook isn’t protected with a password, this argument is ignored.

If you omit this argument for a sheet that’s protected with a password, you’ll be prompted for the password.

If you omit this argument for a workbook that’s protected with a password, the method fails.

If you are trying to modify a protected worksheet, you should run Macro to unprotect > modify > protect

Unprotect worksheet > do something > protect worksheet

Because you cannot manipulate a worksheet while it is protected, when you write a Macro for a protected worksheet, it is best to unprotect a protected worksheet, then run a Macro, finally protect it  back.

Sub proct()
    pwd = "abc123"
    Sheets("Sheet1").Unprotect (Password = pwd)
    'Do something
    Sheets("Sheet1").Protect (Password = pwd)
End Sub

Excel VBA Worksheets.Copy Method to copy worksheet

This tutorial explains how to copy worksheets using Worksheets.Copy Method in Excel VBA, and demonstrate how to copy worksheet to another workbook.

You may also want to read:

Excel VBA Worksheets.Add Method to add new worksheet

Excel Workbooks Open Method and check if workbook open

Excel VBA Consolidate worksheets into one worksheet

Excel VBA Worksheets.Copy Method to copy worksheet

Excel VBA Worksheets.Copy Method is to copy worksheet in a workbook. You can copy worksheets from one workbook to another workbook, and specify to insert after or before specific worksheet.

Syntax of Excel VBA Worksheets.Copy Method

expression .Copy(Before, After)
NameDescription
BeforeOptional. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.
AfterOptional. The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.

If you don’t specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.

Example 1 – Copy worksheet in the same workbook

The below example copies “Sheet2” to the end of the worksheets, and rename it as “new worksheet”.

Public Sub cpy1()
    Sheets("Sheet2").Copy After:=Sheets(ThisWorkbook.Sheets.Count)
    ActiveSheet.Name = "new worksheet"
End Sub

The below example copies “Sheet2” and move to the first worksheet

Public Sub cpy2()
    Sheets("Sheet2").Copy Before:=Worksheets(1)
    ActiveSheet.Name = "new worksheet"
End Sub

Example 2 – Copy worksheet to another workbook

The below example copies worksheet “wbA ws1” to another workbook “workbook b” after worksheet “wbB ws1”

Public Sub cpywb()
    Workbooks("workbook a.xlsx").Worksheets("wbA ws1").Copy after:=Workbooks("workbook b.xlsx").Worksheets("wbB ws1")
End Sub

Example 3 – Copy worksheets to a new workbook

Sub cpyWS()
   Set Newbook = Workbooks.Add
   ThisWorkbook.Sheets("SheetA").Copy After:=Newbook.Sheets(Newbook.Sheets.Count)
   ThisWorkbook.Sheets("SheetB").Copy After:=Newbook.Sheets(Newbook.Sheets.Count)
End Sub

You may also read my another example to copy each worksheet to new workbook.

Example 4 – Copy worksheet from another closed workbook

This example was originally asked in Microsoft Community.

Question

Hi,

Need help with VBA Code.

I’ve searched and tried a few codes but have been having trouble finding something that works.

What I want to do:

1) I’m in an open and active workbook

2) I want to insert and new sheet at the end of all the sheets in the open workbook

3) The sheet I want to insert is from a closed workbook with several worksheets.

Info, Please use in example code, appreciated.

1) location:   “C:\Users\Xxxx\Documents\Xxxx\Target File.xlsx”

Answer

Public Sub cpy()
     Application.ScreenUpdating = False
     Set fileB = Workbooks.Open("C:\Users\Xxxx\Documents\Xxxx\Target File.xlsx")
     fileB.Sheets("Sheet2").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
     fileB.Close
     Application.ScreenUpdating = True
 End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff837784.aspx