CreateObject problem for a CVS file


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 3 123 LastLast
Results 1 to 15 of 36

Thread: CreateObject problem for a CVS file

  1. #1
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111

    CreateObject problem for a CVS file

    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

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    I wouldn't use Shell. Just create the Excel Application object and open the CSV file using automation:

    Code:
    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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    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'.
    Last edited by dmb-job; 06-15-2005 at 04:04 AM.

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    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:

    Code:
    '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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    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
    Last edited by dmb-job; 06-15-2005 at 09:25 PM.

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  7. #7
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    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."
    Last edited by dmb-job; 06-16-2005 at 01:11 AM.

  8. #8
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  9. #9
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    Error occurs at:
    Set objStockDataWB = objExcel.Workbooks.open(DataTablePath)

  10. #10
    Join Date
    Dec 2003
    Posts
    2,750
    I'm not able to reproduce the error. What happens if you try the following:

    Code:
    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)
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  11. #11
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    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.
    Last edited by dmb-job; 06-16-2005 at 06:26 PM.

  12. #12
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    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

  13. #13
    Join Date
    Dec 2003
    Posts
    2,750
    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?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  14. #14
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    Thanks, Paul
    It now works.

  15. #15
    Join Date
    Jul 2004
    Location
    St. Louis, Mo
    Posts
    111
    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
    Last edited by dmb-job; 06-21-2005 at 10:22 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center