Skapa csv file är open macro using

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

You may also want to read:

Import kinesisk CSV to Excel

Excel VBA omvandla CSV to Excel

Excel VBA Import CSV into Excel using Workbooks.OpenText Method

In Excel workbook, you can manually import a CSV en samling dokument eller en elektronisk lagring av data into Excel (Data > From skrivelse / CSV).

Cells(1, 2)

However, you have to select some options in advance such as delimiter. In beställning to import CSV into Excel automatically, you may use Workbooks.Open skrivelse Method.

Syntax of Workbooks.Open ord Method

Name	Required/Optional	Data type	Description
FileName	Required	String	Specifies the en samling dokument eller en elektronisk lagring av data name of the skrivelse en samling dokument eller en elektronisk lagring av data to be opened and parsed.
Origin	Optional	Variant	Specifies the ursprung of the skrivelse en samling dokument eller en elektronisk lagring av data. 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 ord en samling dokument eller en elektronisk lagring av data fryst vatten Arabic (Windows). If this argument fryst vatten omitted, the method uses the current setting of the en samling dokument eller en elektronisk lagring av data ursprung option in the ord Import Wizard.
StartRow	Optional	Variant	The row number at which to uppstart parsing ord. Open (directory & fileName) Where directory is: directory = wb The default value fryst vatten 1.
DataType	Optional	Variant	Specifies the column format of the uppgifter in the en samling dokument eller en elektronisk lagring av data. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. If this argument fryst vatten not specified, Microsoft Excel attempts to determine the column format when it opens the file. Name Value Description xlDelimited 1 Default. Indicates that the en samling dokument eller en elektronisk lagring av data fryst vatten delimited bygd delimiter characters. xlFixedWidth 2 Indicates that the information in the en samling dokument eller en elektronisk lagring av data fryst vatten arranged in columns of fixed widths.
TextQualifier	Optional	Variant	Name Value Description xlTextQualifierDoubleQuote 1 Double quotation mark (“). xlTextQualifierNone -4142 No delimiter. xlTextQualifierSingleQuote 2 Single quotation mark (‘).
ConsecutiveDelimiter	Optional	Variant	True to have consecutive delimiters considered one delimiter. The default fryst vatten False.
Tab	Optional	Variant	True to have the flik character be the delimiter (DataType must be xlDelimited). The default value fryst vatten False.
Semicolon	Optional	Variant	True to have the semicolon character be the delimiter (DataType must be xlDelimited). 2) Creating a CSV file using the FileSystemObject object The default value fryst vatten False.
Comma	Optional	Variant	True to have the comma character be the delimiter (DataType must be xlDelimited). The default value fryst vatten False.
Space	Optional	Variant	True to have the space character be the delimiter (DataType must be xlDelimited). The default value fryst vatten False.
Other	Optional	Variant	True to have the character specified bygd the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value fryst vatten False.
OtherChar	Optional	Variant	(required if Other fryst vatten True). This article shows the 3 ideal examples for applying Excel VBA to Read CSV File Line by Line Specifies the delimiter character when Other fryst vatten True. If more than one character fryst vatten specified, only the first character of the string fryst vatten used; the remaining characters are ignored.
FieldInfo	Optional	Variant	An array containing parse upplysning for individual columns of uppgifter. The interpretation depends on the value of DataType. When the uppgifter fryst vatten delimited, this argument fryst vatten an array of two-element arrays, with each two-element array specifying the konvertering options for a particular column. This Article provides two VBA code samples to create and write to a CSV file: 1) Creating a CSV file using the Open For Output as FreeFile The first element fryst vatten the column number (1-based), and the second element fryst vatten one of the XlColumnDataType constants specifying how the column fryst vatten parsed. Name Value Description xlDMYFormat 4 DMY date format. xlDYMFormat 7 DYM date format. xlEMDFormat 10 EMD date format. xlGeneralFormat 1 General. xlMDYFormat 3 MDY date format. xlMYDFormat 6 MYD date format. xlSkipColumn 9 Column fryst vatten not parsed. xlTextFormat 2 Text. xlYDMFormat 8 YDM date format. xlYMDFormat 5 YMD date format.
TextVisualLayout	Optional	Variant	The visual layout of the text.
DecimalSeparator	Optional	Variant	The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting fryst vatten the struktur setting.
ThousandsSeparator	Optional	Variant	The thousands separator that Excel uses when recognizing numbers. The default setting fryst vatten the struktur setting.
TrailingMinusNumbers	Optional	Variant	Specify True if numbers with a minus character at the end should be treated as negativ numbers. If False or omitted, numbers with a minus character at the end are treated as text.
Local	Optional	Variant	Specify True if regional settings of the machine should be used for separators, numbers and information formatting.

Example – Import CSV into Excel using Workbooks.OpenText Method

Suppose we have a personal list as below in csv en samling dokument eller en elektronisk lagring av data, in which the delimiter fryst vatten comma with double quotation around ord that contains comma (job title).

Uur goal fryst vatten import CSV into Excel and delimit the uppgifter 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 en samling dokument eller en elektronisk lagring av data 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 en samling dokument eller en elektronisk lagring av data will open in Excel and the uppgifter fryst vatten delimited properly.

Note that OpenText Method only opens the CSV in Excel but it fryst vatten not importing the information into the current workbook.

To do so, we can add some codes to kopia 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 fryst vatten added to the current workbook in a new worksheet.

Outbound References

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