CreateObject problem for a CVS file

[dmb-job]

I have just imported a file from the net and it defaults to a CSV, which is great.

Dim ExcelXLS As Object
CSVDatatable = "D:\Documents and Settings\Pough\Desktop\table.csv"

Call Shell(ExcelEXEpath + " " + CSVDataTable, vbMaximizedFocus)
Sleep 10000 ''this does put the CVS data table onto the screen in an Excel workbook, Is there a way to wait for completion?

'this next line appears to be ignored , HELP
Set ExcelXLS = CreateObject("D:\Documents and Settings\Pough\Desktop\table.csv", Excel)

Is a GetObject more appropriate?

I have the CSVDatatable as a user input line so this code can be used in other computers. Therefore, I would like to write the above line something like:
Set ExcelXLS = CreateObject(CSVDataTable, Excel)
'but VB wants to parse the CSVDataTable if I leave the quotes off and generates errors. Plus the Call Shell does not work if I leave the quotes off.

'Then I get an error 438 "Object does not supported this proberty" for the next line
With (ExcelXLS)
If IsEmpty(.Sheets("table").Range("A252")) = True Then etc.
.
.
End With

Winnie_the_Pough

[pclement]

I wouldn't use Shell. Just create the Excel Application object and open the CSV file using automation:

Dim objExcel As Object 'Excel.Application
Dim objExcelWB As Object 'Excel.Workbook

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
Set objExcelWB = objExcel.Workbooks.Open("D:\Documents and Settings\Pough\Desktop\table.csv")

'...
'...

objExcelWB.Close
Set objExcelWB = Nothing
objExcel.Quit
Set objExcel = Nothing

[dmb-job]

Thanks Paul HOWEVER my attempt with your VB will not open my workbook, which is the same problem that I had with attempting to CreateObject.
Maybe there is something that I am doing that is subtitly wrong, or I misspelled or......

Dim objExcel As Object ' ExcelApplication
Dim objStockDataWB As Object ' Yahoo's data, Excel.Workbook

Set objExcel = CreateObject("Excel.Application") 'This loads the generic EXCEL program
objExcel.Visible = True
THERE IS SOMETHING FUNDAMENTALLY WRONG WITH EVERYTHING I HAVE TRIED WITH THE 'SET' LINE THAT FOLLOWS. I HAVE EVEN TRIED WITH A DOT XLS FILE NAME.
Set objStockDataWB = objExcel.workbooks.open(DataTablePath) 'Paul's suggestion
'VB6 does not recognize the above line as it would not capitalize 'workbooks' and 'open' method as far as I can tell,
With (objStockDataWB) 'my code to check to see that VB recognizes objStockDataWB object
.Sheets("table").Range("A1") = Symbol 'this is a line of test code to verify the Open(DataTablePath)
this did not change the cell("A1")
VB does show DataTablePath containing the correct string. I checked it one more time, by stepping through from D: to Documents and....
Am I misspelling this and not seeing the error?
'at this point the CSV data table is on the screen, but hidden by the VB code which has a 'stop' here so that I can look at cell("A1")

DataTablePath is a text box in the form containing D:\Documents and Settings\Pough\Desktop\table.csv without quotes

Under some combination of things I have tried I had the following results:
With no quotes, VB does not show anything, that is, VB does not seem to recognize objStockDataWB at all
but generates an error which I have trapped before I removed that code line: i.e. On Error goto errorhandler1.
If I add quotes, then VB shows objStockDataWB as "Nothing"
I am completely lost. I have used CreatedObject with ExcelXLS files before, but this one is 'bananas'.

[pclement]

OK, let's back up a second. I set up the code to use late binding, which works better with multiple versions of Excel but disables Intellisense and type checking. Try using early binding to turn on Intellisense. This might help you figure out what the problem is.

First add the Microsoft Excel object reference to your project (Project...References...Microsoft Excel x.0 Object Library). Then modify the following code and let us know what happens:

'Declare early bound objects to enable Intellisense
Dim objExcel As Excel.Application
Dim objStockDataWB As Excel.Workbook

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
Set objStockDataWB = objExcel.Workbooks.Open(DataTablePath)

'...
'...

objStockDataWB.Close
Set objStockDataWB = Nothing
objExcel.Quit
Set objExcel = Nothing

[dmb-job]

I exactly copied the Early Binding code. I checked and the Microsoft 10.0 Excel Library is checked in References.
This does not change the outcome.
I had my son verify my coding. He verified that the CSV file is indeed address correctly. He is very PC systems savy but not VB6 savy
As I had observed before, the 'open' in the CreateObject line reverts to lower case 'o' if I force it to uppercase.
If I change the CSV file path from 'Desktop' to 'Recent', then the response is: objStockDataWB = Nothing
The code immediately preceeding the non-working 'CreateObject' just finished replacing the contents of the file I am attempting to access. That file is on the Screen, in the background. Do I need to close it or do I need a different addressing to access it while it is on the screen?
With the 'Desktop' version of the path, VB does not recognize that I have moved the cursor over the 'objStockDataWB' text.
If I change the path to XLS, which also resides on the Destop. I get the same reaction.
------------------------------
I initially tried to make my new program work by modifing the appropriate parts of the following code. That, did not work in this application.
Below is code that works on my computer accessing a different XLS file and a prior version of Excel.
Dim TwsDde
Dim strProgramName As String
strProgramName = "D:\Program Files\Microsoft Office\Office\Excel.exe E:\Jts\Excel\TwsDde.xls"
Call Shell(strProgramName, vbMaximizedFocus)
Sleep 3000

Set TwsDde = CreateObject(Class:="E:\Jts\Excel\TwsDde.xls")
Sleep 500
TwsDde.Sheets("Tickers").Range("D5") = mUserID
Winnie_the_Pough

[pclement]

I'm getting a lot of information here from you but I'm having trouble making heads or tails out of the actual problem.

Could you please identify the first problem you encounter? That would include any errors or descriptions and identifications in the code where the error is occurring.

If something isn't working as expected please identify exactly what you are trying to do.

[dmb-job]

I have just automated the download of historic stock data from Yahoo.
Yahoo by default puts the data as 'table.csv' into 'Temporary Internet Files' but allows me the option to save it elsewhere. I chose the 'desktop' because then I have the option to 'replace'. I am writing this program for a friend and the 'desktop' is easier for him to access should he wants to use the data for a different purpose.

In any event, after the data is loaded, the CSV file is on the screen.
I want to manipulate the data: delete columns, reverse the date sorting order and then move selected portions to another XLS file and another VB program. I have written the data manipulation code for this in VB and I know it will work as I have done the equivalent many time before in VB6 and in Excel macros. My problem is that I cannot get a handle on the Object in this Yahoo data download CSV file.

Retry: 'get the data into the spreadsheet
URL = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol & "&a=" & StartMonth & "&b=" & StartDay & "&c=" & StartYear & "&d=" & EndMonth & "&e=" & EndDay & "&f=" & EndYear & "&g=d&ignore=.csv"
GetDataTable ReturnCode
If ReturnCode <> 0 Then
MsgBox "Data table did not load, try it again from the top"
Exit Sub 'PROBLEMS IN RIVER CITY 'this short indent is my convention so I can find the exits
End If

When the web finishes the download, I am presented with an opportunity to save the file, to which I respond:

SendKeys ("~"), True: Sleep 100 'select 'save' from the choice on the standard save form
SendKeys ("~"), True: Sleep 100 'do the save
SendKeys ("+Y"), True 'Yes, replace the file
Sleep 5000 'give it time to update the Excel prices data table on the desktop, sleep 10000 does not make a difference in the outcome
' I have verified that the Excel file is on the screen in the background and appears on the task bar.

On Error GoTo errorhandler2
Set objExcel = CreateObject("Excel.Application") 'This creates the EXCEL EXE as an object
objExcel.Visible = True

'On Error GoTo errorhandler3
Set objStockDataWB = objExcel.Workbooks.open(DataTablePath)
'for example;DataTablePath = "C:\Documents and Settings\Pough\Desktop\table.csv"

With (objStockDataWB) 'put the symbol into the spread sheet
.Sheets("table").Range("A1") = Symbol
Stop 'temporary code to examine the state of objStockDataWB

'etc. etc.
End With

'error from errorhandler3 = "Function Call on left side of assignment must return a Variant or Object"
Reference my prior post: VB in one case declared "objStockDataWB = Nothing", so it must have decided that 'objStockDataWB' was something.
Sorry, but I forgot to capture this error code before this.

Thanks for sticking with me, Winnie_the_Pough
---------------------
This just floors me, as I have an elaborate VB6 program that does unattended (automated) day trading that I wrote entirely from scratch (Vb6, JavaScript, Excel and my own Neural Network) that is exceedingly more complicated than this. I have manipulated Excel files many time before from VB. This problem is keeping me from interfacing my day trading to a new broker. My broker was bought out. Its just a hobby.
---------------------
"There are two groups of computer users: those who have lost data and those who will."

[pclement]

OK, so I have the error. On which line of code does the error occur? If you're not sure, set a debug breakpoint at the top of your code and step through it line by line until the exception is generated.

[dmb-job]

Error occurs at:
Set objStockDataWB = objExcel.Workbooks.open(DataTablePath)

[pclement]

I'm not able to reproduce the error. What happens if you try the following:

Dim objExcel As Excel.Application
Dim objStockDataWB As Excel.Workbook

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Open DataTablePath
Set objStockDataWB = objExcel.Workbooks(1)

[dmb-job]

Same results !!!!!!!


Dim objExcel As Excel.Application ' Excel
Dim objStockDataWB As Excel.Workbook ' Yahoo's historic stock price data
Dim objHomersWB As Excel.Workbook: ' TargetSpreadSheet, Excel Homer's trading file Workbook

'-------------
I HAVE MOVED THIS CODE TO THE TOP OF THE SUBROUTINE, THUS THE FILE THAT IS LOADED IS THE OLD FILE.
PRIOR TO THIS, THE CODE FOLLOWED THE LOAD OF NEW DATA AND A REPLACEMENT OF THE FILE.



'On Error GoTo errorhandler2
Set objExcel = CreateObject("Excel.Application") 'This loads the generic EXCEL program
objExcel.Visible = True
On Error GoTo errorhandler4
objExcel.Workbooks.open DataTablePath

THERE HAS NEVER BEEN A PROBLEM OF OPENING THE dataTablePath. IT HAS ALWAYS OPENED ON THE SCREEN

On Error GoTo errorhandler3
Set objStockDataWB = objExcel.Workbooks(1) 'MOUSEOVER objStockDataWB IS IGNORED BY VB !!

'Set objStockDataWB = objExcel.Workbooks.open(DataTablePath) 'TEMPORARILY REPLACED WITH THE ABOVE
'for example;DataTablePath = "C:\Documents and Settings\Homer Dillard\desktop\table.csv"

On Error GoTo errorhandler5
With (objStockDataWB) 'check to see that the last row of data is updated
'.Sheets("table").Range("A1") = Symbol 'TEMPORARILY REMOVED
End With
Stop

'---------------------

ERRORHANDLER5 produced ERROR CODE = 438, Object doesn't support this property or method.

THE LINE IN PREVIOUS POSTING
'error from errorhandler3 = "Function Call on left side of assignment must return a Variant or Object"
WAS CAUSED BY MY INCORRECTLY CODING THE 'MSGBOX' AND REFERS TO MY CODING ERROR of THE 'MSGBOX'
haste makes waste, but the original problem is still with me.

[dmb-job]

Thanks, Paul

I will accept 20 lashes with a wet noodle.
Shame,shame on me.
I had even included the equivalent of this as 'what worked before' in my prior posting.!!!
Shame,shame on me.

THIS DOES NOT WORK
With (objStockDataWB)
etc.


BUT THIS DOES
objStockDataWB.Sheets("table").Range("A1") = Symbol

Winnie_the_Pough

[pclement]

Yes the With statement you posted is syntactally incorrect and I had spotted that before. Use of the parenthesis is invalid and they must be removed for the correct syntax.

However, I just wanted to take some baby steps to get you past the initial error, which was creating the Workbook object.

So can I assume you've got everything working now?

[dmb-job]

Thanks, Paul
It now works.

[dmb-job]

I'm back in trouble.
I thought I had it licked when,
objStockDataWB.Sheets("table").Range("A1") = Symbol 'WORKed just fine
---------------------------------------------------------------------------

Set objExcel = CreateObject("Excel.Application") 'This loads the generic EXCEL program
objExcel.Visible = True

Set objStockDataWB = objExcel.Workbooks.open(DataTablePath)
' WHERE DataTablePath = "C:\Documents and Settings\Winnie\desktop\table.csv"

objStockDataWB.Sheets("table").Range("A1") = Symbol 'THIS LINE WORKS

If objStockDataWB.Sheets("table").Range("A252") = "" Then
'THIS DOES NOT WORK, GENERATES ERROR CODE 13
Stop 'temporary debug code
End If
If I mouseover the above statement, I can read the contents of "A252"!!!!

IF IsEmpty(.......) = True Then 'ALSO DOES NOT WORK

NOR DOES
With DataTablePath
If .Sheets("table").Range("A252")="" Then

NOR DOES
With objStockDataWB
If .Sheets("table").Range("A252")="" Then

Lost in the woods, I am Winnie_the_Pough