InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
Solve : Merging Tab Delimited Data to Excel with VB Script or Macro? |
|
Answer» Hello, all 3 columns populated A,B, C from 1 through 14 with data of which I have formulas on the Excel page that pick up the 3 values per row of 14 lines in the text file.I guessed that the workbook existed with the formulas. I didn't use any path names for the files, so the script expected ascii.txt, the script, and Book1.xls to be in the same directory. When I said the script wasn't tested, I meant with real data, the script runs without a syntax error....a logic error is another matter. I'm using Excel 2002, but I doubt it's a great leap forward from Excel 2000. Made a few changes to the script, mainly so you can see what's going on in Excel. I strongly urge you to use paths, especially on the SaveAs. I had a helluva time finding the saved workbook. (hint: it was buried in my user directory) Code: [Select]Const ForReading = 1 Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.OpenTextFile("ascii.txt", ForReading) Set xlApp = CreateObject("Excel.Application") xlApp.DisplayAlerts = False xlApp.Visible = True Set xlBook = xlApp.Workbooks.Open("Book1.xls") Set xlSheet = xlBook.Worksheets(1) xlSheet.Activate For row = 1 To 14 strIn = f.ReadLine arrData = Split(strIn, vbTab, -1) xlApp.Cells(row, 1) = arrData(0) xlApp.Cells(row, 2) = arrData(1) xlApp.Cells(row, 3) = arrData(2) Next f.Close xlBook.SaveAs("Book1.xls") 'use a path xlBook.Close xlApp.Quit I have a similar issue as the OP. I need to import a large .txt file into Excel. The data set in the .txt file is delimited by ":". In any given .txt file, there could be up to 70000 individual numbers separated by colons. Excel attempts to import this data set into a single row, thus exceeding the maximum of number of columns that can be created in Excel. What I would like to do is process the data into Excel via a VB script, similar to the OP. I need the data to populate into a single column, thus filling vertical down the column. Can anyone help? Thanks!Please don't hijack other threads. Many times posts get lost and don't receive the attention they deserve. I'm having a hard time visualizing the input file. Is there a single line with 70000 characters occasionally delimited by a colon? If there are multiple lines, does each line end at the delimiter, or does the delimited data wrap lines? Perhaps you could show the first two or three lines of data. Sorry for the hijack Here are a couple of lines of data -3:-3:-3:-3:-2:-2:-2:-2:-2:-2:-1:-1:-1:-1:-1:0:0:0:0:1:1:1:1:1:1:2:2:2:2:2:3:3:3:3:4:4:5:5:5:6:6:6:7: 7:7:7:8:8:8:8:9:9:10:10:11:11:11:11:12:12:13:13:13:14:14:15:15:15:16:16:17:17:18:18:18:19:19: 19:20:21:21:22:22:23:24:24:25:25:25:26:26:27:27:27:28:29:30:30:31:31:32:33:33:34:34:35:35: 36:36:37:38:38:39:40:40:41:41:42:43:43:44:45:46:47:47:47:48:49:50:50:51:52:53:54:55:55:56:57: 58:59:60:60: I don't think that line breaks actually exist in this data stream....it's just a continuous row of numbers separates by colons. I appreciate the help!I got some strange results on some rows, but it may just be the way I cut and pasted your data. Code: [Select]Const ForReading = 1 Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.OpenTextFile("ascii.txt", ForReading) Set xlApp = CreateObject("Excel.Application") xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Add() Set xlSheet = xlBook.Worksheets(1) xlSheet.Activate row = 0 strIn = f.ReadAll arrData = Split(strIn, ":", -1) For x = 0 To UBound(arrData) row = row + 1 xlApp.Cells(row, 1) = arrData(x) Next f.Close xlApp.Visible = True I didn't write any save code, but you can save the spreadsheet through normal means when it appears. Good luck. That code works great, with one slight problem. I think that I have too many data values to import into a single column in Excel 2003. If I cut the data stream in half, the code imports with no problem. However, if I try to import the entire data file, the code stops with errors. Is there a way for the code to open a new "sheet" once the maximum number of rows is obtained? If not, I make this work. I really appreciate the time and code! I found that Excel 2003 has a maximum of 65,536 rows. Code: [Select]Const ForReading = 1 Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.OpenTextFile("ascii.txt", ForReading) Set xlApp = CreateObject("Excel.Application") xlApp.DisplayAlerts = False Set xlBook = xlApp.Workbooks.Add() maxRows = 65535 row = 0 strIn = f.ReadAll arrData = Split(strIn, ":", -1) For x = 0 To UBound(arrData) If row > maxRows Then row = 0 Set xlSheet = xlBook.Sheets.Add xlSheet.Activate End If row = row + 1 xlApp.Cells(row, 1) = arrData(x) Next f.Close xlApp.Visible = True I used maxRows - 1 in the script. You can play around with this number depending how many entries you need per worksheet. Good luck. Perfect! Thanks for all of the help! |
|