ReDim a(1 To Rows.Count, 1 To Columns.Count) If TypeName(FilesToOpen) = 'Boolean' Then MultiSelect:=True, Title:='Text Files to Open') Any ideas?ĭim myDir As String, fn As String, txt As String, y, delim As Stringĭim a(), n As Long, t As Long, maxCol As IntegerįilesToOpen = Application.GetOpenFilename _ I sort of tried to combine two codes together, one that lets me select the text files, and one that lets me put all the data onto one spreadsheet, but it's not working. I've been googling around on ways to import multiple text files without just specifying the directory in the macro. I've searched this out quite a bit and haven't found a very good answer, other than something related to the primary key or index (which again i do not have).Īny help or suggestion is greatly appreciated. I would like to avoid this as the file is very large and it takes some time to open. When this is done, the file imports flawlessly. lis file in Excel and save it as text with no further modification to the file. In addition, I get the same error if I import into a new table. The table has not changed in between the windows conversion. I know that none of the fields of the destination table are indexed and there is no primary key. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.' 'The changes you requested to this table were not successful because they would create duplicate values in the index, the primary key, or relationship. With Windows 7, it seems that this practice leads to the following error when the file is imported to Access: txt extension (in the file name itself without opening) and was able to import the file into Access without issue. I have a file exported from an accounting system that has no extension. How do I assign a variable to original code page and not have it reset to the default Windows (ANSI)? However, the macro recorder assigns a code page number to the TextFilePlatform property. QueryTable.TextFilePlatform has a number that refers to one of these constants. XlPlatform can be one of these XlPlatform constants.Īnd. This works fine for all parameters except TextFilePlatform. I want to set the connection parameters to variables and reapply them with a different filepath.ĭim TabDelimiter as Boolean, CommaDelimiter as Boolean I am writing a macro that changes the filepath for an existing text file import as a QueryTable.

Excel text import wizard from clipboard