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.)
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.
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.
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
Line Input Function
Then, do you know about string parsing? If not please read up on these in help.
Okay, so now you know one way to read in and parse a csv file. Now for the excel part. Read this tutorial...
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.
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
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
xlSht = Nothing
xlApp = Nothing
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