New at this Excel VBA game and very perplexed

[Greg]

I am trying to export data from a Word form to a Excel spread sheet.

The Word form has three data fields. I want to export the form data to
a new row directly under existing rows in the spreadsheet (i.e., as
each new form is created I can run the macro and export the data to the
growing spreadsheet).

I have this code:

Sub ExportToExcel()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim myWB As String
Dim oFF As FormField
Dim i As Long
myWB = "E:\My Documents\Word\Word Documents\Word Tips\Macros\Working
With Access And Excel\myExportBook1.xls"
Set oXL = New Excel.Application
On Error GoTo Err_Handler
Set oWB = oXL.Workbooks.Open(FileName:=myWB)
Set oSheet = oWB.Sheets("Sheet1")
Dim LastRow As Long
LastRow = oSheet.Cells(Rows.Count, "C").End(xlUp).Row
i = 1
For Each oFF In ActiveDocument.FormFields
oSheet.Cells(LastRow + 1, i).Value = oFF.Result
i = i + 1
Next oFF
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
Excel.Application.Quit
Exit Sub
Err_Handler:
MsgBox myWB & " caused a problem. " & Err.Description, vbCritical,
"Error: " _
& Err.Number
End Sub

It is working in part. By that I mean, when it works it does export
the data as expected. The problem is that it keeps throwing errors
(normal Error 462) on the "Lastrow .... line.

The problem seems to be the the Excel.Application.Quit command is not
working. Even when I get the thing to work by stepping through the
code, the code sticks on the "Exit Sub" line. If I go to the Windows
Task Manager I can see instances of Excel processes still ongoing.

The process seems like it should be simple and straigtforward but
obviously I am missing a big piece. Thanks for your help.

 

[Bob Phillips]

You probably need to qualify the Rows as well

LastRow = oSheet.Cells(oSheet.Rows.Count, "C").End(xlUp).Row


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Greg]

Bob,

Thanks. I sent a lengthy reply earlier but apparently it is lost in
cyberspace.

After making your suggested change I was still seeing a similar problem
(i.e., errors and instances of open Excel applications in Windows Task
Manager). I found that by using:

oWB.Save 'Added this line
Set oSheet = Nothing
Set oWB = Nothing
oXL.Quit 'Added this line
Set oXL = Nothing
'Excel.Application.Quit deleted this line

The problem appears to be resolve. Your tip is beneficial as as an
experiment, I removed the qualifying sHeet and then got compile errors.

Thanks.

 

[Bob Phillips]

Bob,

Thanks. I sent a lengthy reply earlier but apparently it is lost in
cyberspace.

After making your suggested change I was still seeing a similar problem
(i.e., errors and instances of open Excel applications in Windows Task
Manager). I found that by using:

oWB.Save 'Added this line
Set oSheet = Nothing
Set oWB = Nothing
oXL.Quit 'Added this line
Set oXL = Nothing
'Excel.Application.Quit deleted this line

If you recall in your earlier thread, 'Critique of code', that is exactly
what I recommended (I posted it after the main code in a thread where I said
I forgot to tidy-up).

You can't use the Excel.Application.Quit because you aren't in Excel.

The problem appears to be resolve. Your tip is beneficial as as an
experiment, I removed the qualifying sHeet and then got compile errors.

So is your problem resolved now?

 

[Greg]

Bob,

Problem yes. Problems no.

Right now I don't understand the current problem of the problem set to
ask a question.

Thanks for your help.

I'll be back ;-)

 

[Bob Phillips]

LOL!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Greg]

Bob (or others reading this string)

I have experimented with my basic code to export Word field data to a
spreadsheet and added several "bells and whistles" in an attempt to
learn and understand a little more about Excel VBA.

The spreadsheet is simply three colunms of data with a header row.
Each time a Word form is exported to the spreadsheet it adds the data
as a new row and then sorts the data by column 1.

The code appears to be doing what I expect and there are currently no
bugs that I can find. I am providing the code here and ask for any
constructive criticism for making it better, more efficient, or more
robust. Have I done anything that is extraneous or down right stupid.
Thanks for your time.

Sub ExportToExcel()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oDataFile As String
Dim oWBName As String
Dim oSheet As Excel.Worksheet
Dim oFF As FormField
Dim bAppNotRunning As Boolean
Dim bWBFileOpen As Boolean
Dim LastRow As Long
Dim i As Long
Dim myRange As Excel.Range

'Define the Workbook file
oDataFile = "C:\myDataBook1.xls"

'Extract the file name
oWBName = oDataFile
While InStr(oWBName, "\") <> 0
oWBName = Right(oWBName, Len(oWBName) - InStr(oWBName, "\"))
Wend
'Or use the old WordBasic object method
'oWBName = WordBasic.FileNameInfo$(oWBName, 3)

'Check if Excel in running. If not then start Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
bAppNotRunning = True
Set oXL = New Excel.Application
End If
On Error GoTo Err_Handler

'Check if target workbook is already opened. If not, open the workbook
If IsFileOpen(oDataFile) Then
bWBFileOpen = True
Set oWB = oXL.Workbooks(oWBName)
Else
Set oWB = oXL.Workbooks.Open(filename:=oDataFile)
End If
Set oSheet = oWB.Sheets("Sheet1")

'Export the Word field data to the target spreadsheet cells
LastRow = oSheet.Cells(oSheet.Rows.Count, "C").End(xlUp).Row
i = 1
For Each oFF In ActiveDocument.FormFields
oSheet.Cells(LastRow + 1, i).Value = oFF.Result
i = i + 1
Next oFF

'If you dont't want to sort the new data then exclude code between ***
and ***
'***
Set myRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(LastRow +
1, 3))
myRange.Sort Key1:=oSheet.Range("A2"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'***

If bWBFileOpen Then
If MsgBox("Do you want to save changes to the spreadsheet?", vbYesNo,
"Save") = vbYes Then
oWB.Save
End If
Else
oWB.Close SaveChanges:=True
End If
Set oSheet = Nothing
Set oWB = Nothing
If bAppNotRunning Then
oXL.Quit
End If
Set oXL = Nothing
Exit Sub
Err_Handler:
MsgBox oDataFile & " caused a problem. " & Err.Description, vbCritical,
"Error: " _
& Err.Number
If bAppNotRunning Then
oXL.Quit
End If
End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer
Dim errnum As Integer
On Error Resume Next
filenum = FreeFile()
'Attempt to open the file and lock it
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
'Check to see if an error occurred and if so which error
Select Case errnum
Case 0 'No error occurred. File is not opened.
IsFileOpen = False
'File is already opened by another user.
Case 70 'Error number for "Permission Denied." File is already
opened.
IsFileOpen = True
Case Else
Error errnum
End Select
End Function

 

[Bob Phillips]

Greg,

Can't see anything to comment on in that code. You use the same technique
that I use to check if an app is already running, same technique to check if
a file is open <G>.

You have error checking, you ask if the user wants to save if the file was
already open, automatically save if it wasn't, only quit the Excel app if
you instantiated it. All good stuff.

Personally, I would say a good job.

The only small thing I do (that you might not feel is relevant) is that I
use late binding and check oXL using CreateObject, just in case my target
app is not installed. But as I say, that might not be somewhere that you
want or need to go.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Greg]

Bob,

Thanks for the comments and your approbation ;-)

The only small thing I do (that you might not feel is relevant) is that I
use late binding and check oXL using CreateObject, just in case my target
app is not installed. But as I say, that might not be somewhere that you
want or need to go.

To understand the comment above I am going to have to go off an learn
what "late binding" is all about. Can you provide an example of "check
oXL using CreateObject."

Thank you very much for everything that you have shown and taught me.

 

[Greg]

Bob,

Still not sure I fully understand late binding, but do you mean doing
something like this:

Dim oXL as Object

'Check if Excel is installed and already running. If not then start
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
bAppNotRunning = True
On Error GoTo Err_Handler1
Set oXL = CreateObject("Excel.Application")
'Set oXL = New Excel.Application
End If
On Error GoTo Err_Handler2

Thanks.

 

[Bob Phillips]

That is exactly what I mean Greg. The thing to remember with late binding is
that you cannot directly reference any part of the type library in question
(Excel in your case), you must use type Object for all objects, and you must
use the actual constant values not constant names (I have written a little
article at http://xldynamic.com/source/xld.EarlyLate.html on the subject, it
uses Outlook mail in the example, but the principles apply).

As to the example on testing the app, that would be something along the
lines of

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
On Error GoTo 0
If Not oXL Is Nothing Then
bAppRunning = True
Else
On Error Resume Next
Set oXL = CreateObject("Excel.Application")
On Error GoTo 0
If oXL Is Nothing Then
MsgBox "Excel not installed"
Exit Sub
End If
End If

'all okay, so continue

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Greg Maxey]

Thanks Bob. I will have a look at your article.

 

[Greg]

Bob,

I hope that you are still tuned in.

I read your article and I think I understand the concept a little
better. I also read KB article 245115 and it stated that with late
binding you didn't need to have a reference to the specific object
model in the project.

So if I remove the reference to Excel as a referenct then these parts
of my code are throwing errors:

Dim my Range as Excel.Range
....
LastRow = oSheet.Cells(oSheet.Rows.Count, "C").End(xlUp).Row
.....
and
Set myRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(LastRow +
1, 3))
myRange.Sort Key1:=oSheet.Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I used your immediate window ?xlUp to determine the actual values of
xlUp, xlAscending, etc, but errors are still thrown with items like
Key1, Order1, etc,

How would I fix this?

Thanks.

 

[Bob Phillips]

Bob,

I hope that you are still tuned in.

I read your article and I think I understand the concept a little
better. I also read KB article 245115 and it stated that with late
binding you didn't need to have a reference to the specific object
model in the project.

So if I remove the reference to Excel as a referenct then these parts
of my code are throwing errors:

Dim my Range as Excel.Range

Range is an object, so it must be defined as

Dim myRange As Object

...
LastRow = oSheet.Cells(oSheet.Rows.Count, "C").End(xlUp).Row
....
and
Set myRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(LastRow +
1, 3))
myRange.Sort Key1:=oSheet.Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I used your immediate window ?xlUp to determine the actual values of
xlUp, xlAscending, etc, but errors are still thrown with items like
Key1, Order1, etc,

Are you sure it is Key1 etc., that is erroring, not the constants you are
using in the sort? I tried it, and replaced all the constants and it worked
fine.

BTW, what I do is to define the constants outside of the code, in the
declarations, so that I can still use them in the code (better for the
technique), like so

Private Const xlUp As Long = -4162
Private Const xlAscending As Long = 1
Private Const xlGuess As Long = 0
Private Const xlTopToBottom As Long = 1
Private Const xlSortNormal As Long = 0

Of course, even better to add enumerated lists as per the application, and
also use conditional compilation so that you cane easily 'Develop Early,
Release Late; <G>.

 

[Greg]

Bob

Are you sure it is Key1 etc., ...

Apparently not. I added the contants at the module level and changed
the range declaration and it appears to work fine now.

Personnally, unless I change professions, I will probably have no need
for late binding. As I mentioned a in an earlier post I am a dabbler
and not a developer ;-). Still I appreciate your instruction.

Of course, even better to add enumerated lists as per the application, and
also use conditional compilation so that you cane easily 'Develop Early,
Release Late; <G>.

And <EG> might have been more appropriate as I feel that you are subtly
attempting to draw me into the deep end.

Feel feel to expound on that approach if you have the desire. I may
balk and stay in the shallow end ;-)

Thanks again for all of your help. Truly professional.

 

[Bob Phillips]

Okay, here we go, the deep end it is.

What I will do is to post some sample code that is a bit like your code, so
it will be familiar(ish). See what you understand, and query what you don't.

The benefit of this approach is that you can do the development using early
binding, then change to late binding for release without having to change
the code. This is what I do in practice.

It is all driven by the conditional constant, #Run_Test. Set this to False
when developing (which implies early binding so you will need to set a
reference to the Excel type library), when ready to relesae, change the
constant to True, and remember to remove the reference.

Option Explicit

#Const Run_Test = False

#If Run_Test Then
Enum xlDirection
xlDown = -4121
xlToLeft = -4159
xlToRight = -4161
xlUp = -4162
End Enum
Enum xlSortOrder
xlAscending = 1
xlDescending = 2
End Enum
Enum xlYesNoGuess
xlGuess = 0
xlNo = 2
xlYes = 1
End Enum
Enum xlSortDataOption
xlSortNormal = 0
xlTextAsNumbers = 1
End Enum
Enum ExcelConstants
xlTopToBottom = 1
End Enum
#End If

Sub TestViaExcel()
#If Run_Test Then
Dim xlApp As Object
Dim oWB As Object
Dim oSheet As Object
Dim myRange As Object
#Else
Dim xlApp As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim myRange As Excel.Range
#End If
Dim LastRow As Long

#If Run_Test Then
Set xlApp = CreateObject("Excel.Application")
#Else
Set xlApp = New Excel.Application
#End If

xlApp.Visible = True

Set oWB = xlApp.workbooks.Open("C:\myTest\Volker1.xls")
Set oSheet = oWB.worksheets(1)

LastRow = oSheet.Cells(oSheet.Rows.Count, "C").End(xlUp).Row

Set myRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(LastRow + 1,
3))
myRange.Sort Key1:=oSheet.Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


Set oSheet = Nothing
Set oWB = Nothing
xlApp.Quit

Set xlApp = Nothing

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Greg]

Bob,

I see how it works and how it would be a best practice for developing.


Perhaps I am venturing deeper, but the only thing that I don't really
follow is the meaning and effect of the "#" symbol leading several of
the lines of code.

 

[Bob Phillips]

That indicates conditional statements to the compiler.

Check 'Understanding Conditional Compilation' in VBA help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Greg]

Bob,

Will do. I suppose that this closes this string.

Been a nice swim. Enen the deep end.

Thanks again.

 

[Bob Phillips]

Pleasure, I enjoyed the journey too <g>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)