DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: VB6 can't open excel sheet after it's created

  1. #1
    Join Date
    Jun 2008
    Posts
    5

    Question VB6 can't open excel sheet after it's created

    Hi,
    In a VB6 pgrm, I'm creating an Excel sheet and save it with .xls. When I double click on the file itself on a machine with Excel 2003 installed, Excel open and hang without the file!
    I use late binding in my code. My code is developed on a machine with Excel 2007 but I save the file in .xls .
    Here is the
    starts:
    Here is the declaration part:
    Public xlApp As Object
    Public xlBook As Object
    Public xlSheet As Object
    Here is the sub routine that starts filling the sheet
    Private Sub FillExcelSheet()

    'set objects for excel and set the headers for the excel sheet
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add
    Set xlBook = xlApp.ActiveWorkbook
    Set xlSheet = xlApp.ActiveSheet
    xlApp.Visible = False

    xlSheet.Activate
    xlSheet.Cells(1, 1) = "Monthly Statistics for"
    xlSheet.Range("A1:F2").Interior.Color = RGB(255, 255, 204)
    xlSheet.Cells(1, 3) = ComboCompName.Text
    xlSheet.Cells(2, 1) = "Date / Year"
    xlSheet.Cells(2, 3) = MonthName(Month(Date)) & Year(Date)

    End Sub

    'here is the end of the event procedure when I'm done with Excel (I don't want to close it in case the user wants to see it now).

    xlSheet.Columns.AutoFit
    xlApp.Visible = True

    xlBook.SaveAs (App.Path & "\" & Year(Date) & "-" & Month(Date) & " Monthly Statistics for " & ComboCompName.Text & ".xls")
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

    Well, hopefully someone can help me.
    thanks,
    MFB

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    The SaveAs() Method has other parameters, including a File Format. You need to select a FileFormat that is compatable with Excel 2003. Open any Excel file, open any VBA Code and add 'ActiveWorkbook.FileFormat' to it
    then highlight 'FileFormat' and hit the F1 key to open the help. From there follow the links to the list of FileFormats available. Choose one that is old enough for Excel 2003; but is still an Excel file. There are other file formats that are not Excel Files.

  3. #3
    Join Date
    Jun 2008
    Posts
    5
    Thanks for your help.
    MFB

  4. #4
    Join Date
    Jun 2008
    Posts
    5

    Question VB6 can't open excel sheet after it's created

    I tried to put the FileFormat param with the SaveAs method. I got an error msg that "SaveAs method of Workbook class failed"
    What should I try now?
    please, someone help me.
    Thanks,
    MFB1
    MFB

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Where are you trying to save it to? Do you have rights to the folder?

  6. #6
    Join Date
    Jun 2008
    Posts
    5

    Question VB6 can't open excel sheet after it's created

    I did have access to it. I ran the vb6.exe application on a machine with Excel 2003 on it. I had access to save the file in the folder. The file is saved with success. If we open Excel and try to open the file created by the vb6 application, it works but if we try to open the file created by the vb6 application in double clicking on it, Excel will open and hang with no files opened. I don't know what t do? Should I really develop on the 2003 machine instead of the 2007?
    I can't use the FileFormat param in SaveAs method.


    thanks for trying to help me.
    Hopefully you will keep helping me.
    Thanks
    MFB1
    MFB

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    What do your customers have, Excel 2003 or Excel 2007?

    You should develop on the same platform they are using.

  8. #8
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    The FileFormat parameter is a numeric value which is supplied by using the Excel Object Library Enums. For each version 2003, vs 2007, can have Enums that don't exist in previous or Older Versions. If you choose a Enum from version 2007 then it may not exist in version 2003. But if you choose one from 2003, then it will exist in 2007. So the SaveAs should not fail.

  9. #9
    Join Date
    Jun 2008
    Posts
    5

    Smile VB6 can't open excel sheet after it's created

    Great! it works.
    Thanks for your help.
    MFB

Similar Threads

  1. VB6 GroupProject - open form in another project
    By Lennie in forum VB Classic
    Replies: 1
    Last Post: 01-28-2008, 10:17 PM
  2. Replies: 2
    Last Post: 07-17-2007, 09:42 PM
  3. vb6 and excel
    By Spumbu1977 in forum VB Classic
    Replies: 4
    Last Post: 07-12-2006, 07:50 PM
  4. Replies: 8
    Last Post: 01-09-2003, 10:42 PM
  5. open connection to excel from a webpage
    By fareeda in forum Web
    Replies: 1
    Last Post: 06-26-2002, 07:21 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