Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.OpenText StartRow:=2 Not Skipping Row 1
The following macro imports the .csv file starting at row 1 of the file:
Sub OpenCSV() Workbooks.OpenText Filename:="C:\Categories.csv", StartRow:=2, DataType:=xlDelimited, Origin:=437, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Semicolon:=False, Comma:=True ActiveWorkbook.Activate ActiveWindow.Visible = True End Sub Where Categories.csv contains: CategoryID,CategoryName,Description CategoryID,CategoryName,Description 1,Beverages,"Soft drinks, coffees, teas, beers, and ales" 2,Condiments,"Sweet and savory sauces, relishes, spreads, and seasonings" 3,Confections,"Desserts, candies, and sweet breads" 4,Dairy Products,Cheeses 5,Grains/Cereals,"Breads, crackers, pasta, and cereal" 6,Meat/Poultry,Prepared meats 7,Produce,Dried fruit and bean curd 8,Seafood,Seaweed and fish Please help me understand why this happens (Excel 2003). -- John Saunders johnwsaunders at hotmail.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.OpenText StartRow:=2 Not Skipping Row 1
When you use your code with a CSV file it basically opens the CSV file, as
opposed to importing its data. Someone else may know why. you can definitely see an Excel native analogy by coosing Open from the File Menu for both a CSV file and then again for a .TXT file. One step launches the import wizard (Text File) and one does not (CSV file). What you CAN do, which mimics the functionality of choosing Data--Import External Data, is code like the below: Sub OpenCSV() With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Categories.csv", _ Destination:=Range("A1")) .TextFilePlatform = 437 .TextFileStartRow = 2 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileCommaDelimiter = True .Refresh BackgroundQuery:=False ''''I see no value to these lines but I may be missing something ActiveWorkbook.Activate ActiveWindow.Visible = True '''' ''''THESE OTHER PROPERTIES SEEM TO BE OPTIONAL IN THIS CASE ' .Name = "MyFile" ' .FieldNames = True ' .RowNumbers = False ' .FillAdjacentFormulas = False ' .PreserveFormatting = True ' .RefreshOnFileOpen = False ' .RefreshStyle = xlInsertDeleteCells ' .SavePassword = False ' .SaveData = True ' .AdjustColumnWidth = True ' .RefreshPeriod = 0 ' .TextFilePromptOnRefresh = False ' .TextFileConsecutiveDelimiter = False ' .TextFileTabDelimiter = False ' .TextFileSemicolonDelimiter = False ' .TextFileSpaceDelimiter = False ' .TextFileColumnDataTypes = Array(1, 1, 1) ' .TextFileTrailingMinusNumbers = True End With End Sub "John Saunders" <johnwsaunders at hotmail.com wrote in message ... The following macro imports the .csv file starting at row 1 of the file: Where Categories.csv contains: CategoryID,CategoryName,Description CategoryID,CategoryName,Description 1,Beverages,"Soft drinks, coffees, teas, beers, and ales" 2,Condiments,"Sweet and savory sauces, relishes, spreads, and seasonings" 3,Confections,"Desserts, candies, and sweet breads" 4,Dairy Products,Cheeses 5,Grains/Cereals,"Breads, crackers, pasta, and cereal" 6,Meat/Poultry,Prepared meats 7,Produce,Dried fruit and bean curd 8,Seafood,Seaweed and fish Please help me understand why this happens (Excel 2003). -- John Saunders johnwsaunders at hotmail.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks.OpenText StartRow:=2 Not Skipping Row 1
Thanks, William, I'll give that a try.
The extra lines were in the context of the macro, and were meant to make the new workbook visible so I could see that the OpenText had failed! :-) -- John Saunders johnwsaunders at hotmail.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbooks.OpenText does not work on XP. HELP !!! | Excel Programming | |||
How to open UTF-8 files with Workbooks.OpenText | Excel Programming | |||
After OpenText all pastes from external apps use format from OpenText | Excel Programming | |||
Workbooks.OpenText | Excel Programming | |||
Using Workbooks.OpenText to read a tab-delimited file into a worksheet | Excel Programming |