-
Sep 18th, 2020, 03:46 PM
#1
Thread Starter
Fanatic Member
[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
-
Sep 18th, 2020, 03:56 PM
#2
Re: Writing to Excel
First, what is the ETF after the strFileName?
Sam I am (as well as Confused at times).
-
Sep 18th, 2020, 04:01 PM
#3
Thread Starter
Fanatic Member
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.
-
Sep 18th, 2020, 04:31 PM
#4
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:
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.
-
Sep 18th, 2020, 05:33 PM
#5
Thread Starter
Fanatic Member
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.
-
Sep 18th, 2020, 05:37 PM
#6
Thread Starter
Fanatic Member
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
-
Sep 18th, 2020, 05:39 PM
#7
Re: Writing to Excel
Originally Posted by AccessShell
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.
-
Sep 18th, 2020, 06:14 PM
#8
Thread Starter
Fanatic Member
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.
-
Sep 18th, 2020, 06:16 PM
#9
Thread Starter
Fanatic Member
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!
-
Sep 18th, 2020, 06:51 PM
#10
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.
-
Sep 18th, 2020, 06:58 PM
#11
Thread Starter
Fanatic Member
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?
-
Sep 18th, 2020, 10:17 PM
#12
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|