Excel import text to cells

HKEY_LOCAL_LOSER
HKEY_LOCAL_LOSER used Ask the Experts™
on
Need help to input text into a spreadsheet. Need the peak data (6-8 digit number)
in the Standard tab and also the Std#*-****** *****rep* to go in the top row for each one.
I would really appreciate it.
Report-Macro--1-.xlsm
Report-Data.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I'd be happy to help but your spreadsheet is rather confusing and the source data does not correlate well to your description.

Using your source text file we can easily grab the data.  Here is a sample that reads your report file, then fills in the DataFileName and the AcrylicAcidPeakArea into a spreadsheet.  It starts inserting data at row 83 and puts the DataFileName in column A and the AcrylicAcidPeakArea into column C.  Note that in VBScript you must refer to the columns by number instead of letter, so columns 1 & 3 in this example.

It might be easier for you to build an entirely new spreadsheet with your data and do calculations within the script rather than trying to do randomly insert data into an existing spreadsheet.
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
'This will use an Excel spreadsheet that is already open
Set objExcel = GetObject(,"Excel.Application)
ExcelRow = 83



Set oTextStream = oFSO.OpenTextFile("wslist.txt")
'make an array from the data file
ReportData = Split(oTextStream.ReadAll, vbNewLine)
'close the data file
oTextStream.Close

'Data starts at 3rd row
datarow = 3

Do Until datarow = UBound(ReportData)
	thisData = Split(ReportData(datarow), vbTab)
	DataFileName = thisData(0)
	MultFactor = thisData(1)
	SampleA = thisData(2)
	Vial = thisData(3)
	UserName = thisData(4)
	AcrylicAcidPeakArea = thisData(5)
	IsoparHPeakArea = thisData(6)
	
	'Now you can fill in the data anywhere you want
	objExcel.cells(ExcelRow,1).Value = DataFileName
	objExcel.cells(ExcelRow,3).Value = AcrylicAcidPeakArea
	
	ExcelRow = ExcelRow + 1
	datarow = datarow + 1
Loop

Open in new window

Commented:
oops, please note you need to change the report name.  I used wslist.txt in my example.  include the path to the file if it will not be in the same directory as the script.

Author

Commented:
I get the error  runtime 424 "Object required" on the "Set oTextStream" line.
What I was trying to do is create a button on the first tab, separate from the Standard tab (2nd one)
to prompt for selecting a text file to import. I had a subroutine like this but is there a way I can incorporate it into your script?
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
I've requested that this question be deleted for the following reason:

I did not intend for this information to be available publicly

Commented:
HKEY_LOCAL_LOSER You need to set the path to the source file for the oTextStream line to work.

Moderator, having spent time to create the script for the OP, I object to the question being deleted.  Rather please remove any proprietary data in the attachments from the original post.

Author

Commented:
Thanks Mark, If this is possible I would think it to definitely be a better solution.
As for the file path, what would activate the file being imported, and if the file changes, can it still be done?

Author

Commented:
Sorry by file changing I meant the file name and the numbers only, not the placement of the data, just to clarify.

Commented:
Hkey local loser, executing the script outside of Excel will force the import. Or you could try to program a button with VBA.

Commented:
Moderator, I object to closing the question having provided a script for it, rather please just delete the attachments that contain private data.

Author

Commented:
aikimark:

I'm not seeing anywhere to edit the post on this side. Please forgive any ignorance as I am  pretty new here. Where can I go to do this? Should I repost the question?

Author

Commented:
Here are the modified files. I do appreciate your undersanding and will avoid this scenario in the future.

Markd, if I were to call a button to do this fso import what would that look like? I think it would be better to do it within Excel.
Report-Macro.xlsm
Report-Data.txt

Commented:
HKEY_LOCAL_LOSER, are you seeing the data populate into Excel now?  

Author

Commented:
No but it's ok, I'm sure you had the right idea. I'll give you a double E for effort in Excel. :)

Author

Commented:
Yes aikimark I meant to close the question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial