Copy CSV to another Open Excel worksheet

[gtdalsanto]

I am trying to find an easy way to copy a CSV excel file into a worksheet in a totally separate excel spreadsheet.

What I have so far is a CSV file that I have generated from a data file. Once the CSV file is created, I open it up in excel, and perform a select all, then a control c, which copies the spreadsheet to the clipboard. This spreadsheet is the data that will be used in another spreadsheet.

At this point, I open another spreadsheet, where the first 2 worksheets are graphs, totals, etc. But on the third worksheet, this is where all the data is placed that is used by the first 2 sheets.

I am trying to figure out an easy way to paste the contents of the clipboard (the csv file) into the 3rd sheet/tab in the excel spreadsheet. Also, I need to be able to paste this data starting at a location other than A1. For my purposes, I need to paste the csv file spreadsheet at A6 in the third sheet of the spreadsheet.

I originally created a script that wrote the data to the excel spreadsheet cell by cell. This solution however became too slow. The other way to do this would be to just write a macro scheduler script to do this. However, since this data could be pasted to the 3rd sheet this month, it will change as time goes on. Each worksheet designates a quartere. That is why I am trying to find a way that I can take the clipboard data and just copy it to a specific sheet and a specific cell.

Any help on this problem would be appreciated.

Thanks.

Gary

[Marcus Tettmar]

Here you go:

Code: Select all

//Put this VBSTART..VBEND block at top of script
//to declare the functions once
VBSTART
Dim xlApp
Dim xlBook
Dim CSVBook

'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.open(filename)
end sub

Sub OpenCSVFile(filename)
  Set CSVBook = xlApp.Workbooks.open(filename)
end sub

Sub CopyPasteCSV(toSheet,startCell)

Dim xlToRight
Dim xlDown

	xlToRight=-4161
	xlDown=-4121

	CSVBook.ActiveSheet.Range("A1").Select
	CSVBook.Activesheet.Range(xlApp.Selection, xlApp.Selection.End(xlToRight)).Select
    CSVBook.Activesheet.Range(xlApp.Selection, xlApp.Selection.End(xlDown)).Select
    xlApp.Selection.Copy

	xlBook.Activate
	xlBook.Sheets(toSheet).Select
	xlBook.sheets(toSheet).Range(startCell).Select
	xlBook.sheets(toSheet).Paste

End Sub

'Use this to close Excel later
Sub CloseExcel
   xlApp.quit
   Set xlApp = Nothing
End Sub

Sub CloseCSV
   CSVBook.close
End Sub

'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  GetCell = xlSheet.Cells(Row, Column).Value
End Function

'Sets specified cell of specified worksheet
Function SetCell(Sheet,Row,Column,NewValue)
  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Cells(Row,Column).Value = NewValue
End Function
VBEND

//open example.xls
VBRun>OpenExcelFile,%SCRIPT_DIR%\example.xls

//open your CSV file
VBRun>OpenCSVFile,%SCRIPT_DIR%\example.csv

//Copy and paste CSV to
VBRun>CopyPasteCSV,Sheet2,B5

//Close the CSV book
VBrun>CloseCSV

This opens example.xls, which has three sheets. It then opens a CSV file in Excel in a different workbook. It then copies the full CSV content to the clipboard and then activates Sheet 2 of example.xls and pastes the CSV content at starting position B5. Hope it makes sense.

Here's a zip file containing script, example.xls and example.csv. Pop them all into the same directory and run the script to see it work.

http://www.mjtnet.com/scripts/csvtoxl.zip

[gtdalsanto]

Thanks!!! That is exactly what I was looking for. Thanks again for your quick response.

Gary

[allthenames]

This is almost exactly what I'm looking for. Except maybe a lot different. I am trying to create web pages from an excel sheet using each line for a page and each cell as a variable. Therefore it would require the program to go through each line one at a time as it creates each page.

I don't know if it is even possible to output html code to a file.

I don't know if i'm not thinking the right way to achieve what I would like.

Is this even possible?
Thank for your time.

[allthenames]

Let>comma=,
Separate>line,comma,fields
ReadFile>C:\Program Files\screen-scraper basic edition\water.txt,TheFileData
Separate>TheFileData,CRLF,Lines
Let>comma=,
Let>k=1
Repeat>k
Let>CurrentLine=Lines_%k%
//extract each field
Separate>CurrentLine,comma,fields
Let>filename=%fields_1%.txt
WriteLn>C:\Users\Ben's Asus\Documents\%filename%,result,%fields_1%%fields_1%
Let>k=k+1
Until>k>Lines_Count


I've managed to output the html code to a file however I would like to save each file as a variable. Right now i am only producing one file called fields_1.

Also it appears that the fields_1 variable is extracting data from 2 rows on each line the first row is the item id which is what I would like the data to be saved as the second row is the title.

Thanks again.

[allthenames]

Sorry that I'm thinking out loud. I believe I've realized my problem. I believe because the %fields_1% variable contains two columns it is creating an invalid file name. Is there any way to limit those results to each fields variable to represent one column?

P.S these are "tab delimited" values

Thanks