Results 1 to 12 of 12

Thread: [RESOLVED] Writing to Excel

  1. #1

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Resolved [RESOLVED] Writing to Excel

    I am running VB6 on WIN10

    I need to write to two different Excel workbooks. The code is almost identical as shown below:

    Code:
    Private Function WriteToHistoryH()
    
        'Get the file name
        strDirectory = "C:\My VB6 Executables\Details\Details\"
        strFileName ="History H.xls"ETF 
        
        'create Excel object
        Set ExcelApp = CreateObject("Excel.Application")
    
    
        'open the workbook
        Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
        Set ExcelSheet = ExcelWorkbook.Worksheets(1)
        
         'Add Today's Data
        With Sheets("HistoryH")
    
             ....Detail code here.....
    
    '***  Close the  Workbook
        ExcelWorkbook.Save
    
    
        'Close Excel
        ExcelWorkbook.Close savechanges:=False
        ExcelApp.Quit
        Set ExcelApp = Nothing
        Set ExcelWorkbook = Nothing
        Set ExcelSheet = Nothing
       
    End Function
    The second function is called Private Function WriteToHistoryV(). The strFileName is changed to "History V.xls" . And the With Sheets("HistoryH") is changed to With Sheets("HistoryV")

    No matter which order I run these two procedures, the second one crashes. I get Run-time error '1004'; Method 'Sheets' of object "_Global' failed

    Can anyone explain to me what is happening? Did I cone something wrong?

    Thanks

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Writing to Excel

    First, what is the ETF after the strFileName?
    Sam I am (as well as Confused at times).

  3. #3

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: Writing to Excel

    It is a type. It is not in the original code. I am sure it did not come over when I copied the code into the post.

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Writing to Excel

    Also, this code ...

    Code:
        Set ExcelSheet = ExcelWorkbook.Worksheets(1)
        
         'Add Today's Data
        With Sheets("HistoryH")
    ... looks very strange to me.

    It seems that the "With" block should start with:

    Code:
    With ExcelSheet
    If not, why'd you create the ExcelSheet object in the first place?

    Also, where are all these object variables declared?

    -----

    EDIT: Basically, I write to two Excel sheets quite often in my code, but your posted code looks like a bit of a mess. Are you using "Option Explicit"? If not, I'd highly recommend it, as it might solve many of your problems.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  5. #5

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: Writing to Excel

    I am using Option Explicit. I always use Option Explicit. I did not add the code in the post that contains all the declarations. Also, the way I have written code has worked for me for over 20 years. This is the first time I tried to use tow different workbook in onw program. I will say that sith my code, there I times the updates are entered into a different sheet that I referenced I get around that my only writing to workbooks that have only one worksheet.

  6. #6

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: Writing to Excel

    Here are the declarations that you referenced

    Code:
    Dim ExcelApp      As Excel.Application
    Dim ExcelWorkbook As Excel.Workbook
    Dim ExcelSheet    As Excel.Worksheet
    Dim strDirectory   As String
    Dim strFileName    As String

  7. #7
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Writing to Excel

    Quote Originally Posted by AccessShell View Post
    I am using Option Explicit. I always use Option Explicit. I did not add the code in the post that contains all the declarations. Also, the way I have written code has worked for me for over 20 years. This is the first time I tried to use tow different workbook in onw program. I will say that sith my code, there I times the updates are entered into a different sheet that I referenced I get around that my only writing to workbooks that have only one worksheet.
    Ahh, ok. So, I assume your object variables are scoped at least at the module level (if not global to the project).

    So, when simultaneously manipulating two workbooks (i.e., Excel files), you will have to duplicate your workbook variable, as well as any other downstream variables (worksheet, range, etc). This will be necessary to keep the two workbooks straight.

    The only variable you'll only need one of is the actual Excel application object variable itself.

    So, something like this:

    Code:
    
    Dim ExcelApp       As Excel.Application
    Dim ExcelWorkbook1 As Excel.Workbook
    Dim ExcelSheet1    As Excel.Worksheet
    Dim ExcelWorkbook2 As Excel.Workbook
    Dim ExcelSheet2    As Excel.Worksheet
    
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  8. #8

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: Writing to Excel

    No. The variables are declared at the function level. The variables just happen to have the same names. I will try to use different variable names in each function and see what happens.

  9. #9

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: Writing to Excel

    However, since I do this
    Set ExcelApp = Nothing
    Set ExcelWorkbook = Nothing
    Set ExcelSheet = Nothing

    [/CODE]
    at the end of each function, I do not see why that matters!

  10. #10
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Writing to Excel

    Ok, my mistake. If all the variables are scoped within the function, then you should be able to use the same names. Also, for object variables, you can, but there's no need to set them to "Nothing". When they fall out of scope, that'll happen automatically.

    The one thing you'd be doing though that I wouldn't do is to instantiate two copies of the entire Excel program. That ExcelApp variable, I'd scope (and instantiate) it wider than the function level.

    Beyond that, it should work. However, I'm still very confused about that With Sheets("HistoryH") line you have in your Original Post. To my eyes, that looks more like VBA code than VB6 code.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  11. #11

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: Writing to Excel

    I thought that "With Sheets(NAME)" is the worksheet in the workbook. This way if you have several sheets how would the code know which sheet to work on?

  12. #12

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    790

    Re: Writing to Excel

    OK, so I solved the problem. I don't really understand what I did, but it is solved. In addition, I found out why sometimes the data added was placed into the wrong sheet. WHatever was the active sheet when the file was opened is where the data was added. It was as if the With statement was doing nothing.


    OK, so here is the new code, much simpler. Different routines. Different spreadsheet names, etc.

    Code:
    Option Explicit
    
    
    Dim ExcelApp      As Excel.Application
    Dim ExcelWorkbook As Excel.Workbook
    Dim ExcelSheet    As Excel.Worksheet
    Dim strDirectory   As String
    Dim strFileName    As String
    
    
    Sub main()
    
    
        strDirectory = "C:\My VB6 Executables\Excel Test\"
        strFileName = "Family.xls"
    
    
        Sister
        Dad
        Mom
        Brother
        
    End Sub
    
    
    Private Function Mom()
        
        'create Excel object
        Set ExcelApp = CreateObject("Excel.Application")
    
    
        'open the workbook
        Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
        Set ExcelSheet = ExcelWorkbook.Worksheets(1)
        
         'Add Today's Data
        With ExcelApp
            .Sheets("Mom").Select
            .Range("B" & 1).Value = "Mom"
        End With
       
    '***  Close the  Workbook
        ExcelWorkbook.Save
    
    
        'Close Excel
        ExcelWorkbook.Close savechanges:=False
        ExcelApp.Quit
        Set ExcelApp = Nothing
        Set ExcelWorkbook = Nothing
        Set ExcelSheet = Nothing
    
    
    End Function
    
    
    Private Function Dad()
        
        'create Excel object
        Set ExcelApp = CreateObject("Excel.Application")
    
    
        'open the workbook
        Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
        Set ExcelSheet = ExcelWorkbook.Worksheets(1)
        
         'Add Today's Data
        With ExcelApp
            .Sheets("Dad").Select
            .Range("E" & 6).Value = "Dad"
        End With
       
    '***  Close the  Workbook
        ExcelWorkbook.Save
    
    
        'Close Excel
        ExcelWorkbook.Close savechanges:=False
        ExcelApp.Quit
        Set ExcelApp = Nothing
        Set ExcelWorkbook = Nothing
        Set ExcelSheet = Nothing
    
    
    End Function
    
    
    Private Function Brother()
        
        'create Excel object
        Set ExcelApp = CreateObject("Excel.Application")
    
    
        'open the workbook
        Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
        Set ExcelSheet = ExcelWorkbook.Worksheets(1)
        
         'Add Today's Data
        With ExcelApp
            .Sheets("Brother").Select
            .Range("F" & 7).Value = "Brother"
        End With
       
    '***  Close the  Workbook
        ExcelWorkbook.Save
    
    
        'Close Excel
        ExcelWorkbook.Close savechanges:=False
        ExcelApp.Quit
        Set ExcelApp = Nothing
        Set ExcelWorkbook = Nothing
        Set ExcelSheet = Nothing
    
    
    End Function
    
    
    Private Function Sister()
        
        'create Excel object
        Set ExcelApp = CreateObject("Excel.Application")
    
    
        'open the workbook
        Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
        Set ExcelSheet = ExcelWorkbook.Worksheets(1)
        
         'Add Today's Data
        With ExcelApp
            .Sheets("Sister").Select
            .Range("D" & 4).Value = "Sister"
        End With
       
    '***  Close the  Workbook
        ExcelWorkbook.Save
    
    
        'Close Excel
        ExcelWorkbook.Close savechanges:=False
        ExcelApp.Quit
        Set ExcelApp = Nothing
        Set ExcelWorkbook = Nothing
        Set ExcelSheet = Nothing
    
    
    
    
    End Function
    I will close this post. But maybe you can answer the questions in this post? And maybe evaluate the new code. I may not have to set "somethings" to nothing, but I feel better.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width