Put csv file into Excel worksheet

[rclinebell]

I need to put data from a csv file into an Excel worksheet.
File is called "DTN.csv" and the Excel file is "BlendPrice" and the workbook is "DTNPrice".
The DTN.csv file has 12 fields seperated by commas. (I have been copying and pasting the data in now. I need to automate this process to run at night.)

[vb5prgrmr]

Several ways...
Several Questions...

Do you want your program to run as a service?
Do you know how to open a file and read it in?
Do you know how to automate excel?
Do you know how to use excel like a database with ADO?
Do you know how to use the Timer control?
If not a service, do you want to have your program launched as a task?
Where or with what part of the code you are working with do you have problems?

I could probably come up with a few more but these should get you started.

Good Luck

[rclinebell]

I'm needing to run the VB.exe program during the night. I have a CSV file coming in over the internet that I need to paste the line items into a worksheet in excel. I have been opening the file and doing a copy and paste routine for now. I would like to automate this step so it is ready without my efforts for all in the organization. Since this is an everyday routine, the automation would be great.

I just need to place the DTN.CSV file, line by line into an Excel file called DtnPricing and a worksheet called DTNimported. The lookup rountines from other worksheets within the Excel file take it from there and make the information useful to our organization.

[vb5prgrmr]

Okay, so for now... you don't mind starting the program before you leave, right?

One step at a time...

First, do you know how to read the file in by using VB? If not please look these up in help

Open Statement
FreeFile Function
Input Function
Line Input Function
Close Statement

Then, do you know about string parsing? If not please read up on these in help.

Left Function
Right Function
Mid Function
Trim Function
Replace Function
Split Function
Join Function

Okay, so now you know one way to read in and parse a csv file. Now for the excel part. Read this tutorial...

http://www.vbforums.com/showthread.php?t=391665

Need help with above, please post back in a new thread with the url of this thread referenced with the code that you are having problems with.

Good Luck

[rclinebell]

Here is the code I have come up with so far. I'm still getting error.

Private Sub Form_Load()

Dim xlApp As Excel.Application
Dim xlSht As Excel.Worksheet
Dim xlRng As Excel.Range

xlApp.Workbooks.Open ("Fuel_PricesBYDTN.xls")
xlSht = xlApp.Sheets(1)
xlRng = xlSht.Cells(1, 1)

Open "00001DTN.CSV" For Input As #1

I = 0
Do Until EOF(1)
I = I + 1
Input #1, A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12

xlSht.Cells(I, 1) = A1
xlSht.Cells(I, 2) = A2
xlSht.Cells(I, 3) = A3
xlSht.Cells(I, 4) = A4
xlSht.Cells(I, 5) = A5

Loop

xlApp.Workbooks.Close()
ReleaseComObject (xlRng)
ReleaseComObject (xlSht)
ReleaseComObject (xlApp)
xlSht = Nothing
xlApp = Nothing

Cls #1

Cls

End Sub

[vb5prgrmr]

Where are you getting the error? On the open line? Put full path to file as arguement ("C:\SomeDir\SomeFile.xls")

Do you know how to debug? Press F8 to walk through and post error number and description.

Example error handler...

Private Sub Form_Load()

On Error GoTo Form_LoadError

Exit Sub
Form_LoadError:

MsgBox Me.Name & " Form_Load " & Err.Number & ":" & Err.Description

End Sub

Good Luck