We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

why does this Excel VBA code just exit without proceeding?

stmoritz
stmoritz asked
on
Medium Priority
621 Views
Last Modified: 2012-05-11
it just exits/stops after this, without any error message, after opening the SourceWb

Set SourceWb = Workbooks.Open("C:\!ee\AgreementBase\ee_company_details_BANANA.xls")


Sub FetchDataFromIndividualFilesToDb()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.AutomationSecurity = msoAutomationSecurityForceDisable

'dim the variables
Dim ActiveCompany As String, ActiveCompanyRowInDb As Integer, LastRowAgreementDb As Integer, ActiveCompanyRow As Integer, LastColTargetWs As Integer

'dim the ranges
Dim TargetRange As Range, SourceRange As Range, SortRange As Range, SourceCompanyCodeRange As Range, TargetCompanyCodeRange As Range

'dim the workbooks
Dim TargetWb As Workbook, SourceWb As Workbook

'dim the worksheets
Dim TargetWs As Worksheet, SourceWs As Worksheet

'set and open workbooks
Set TargetWb = ActiveWorkbook
Set SourceWb = Workbooks.Open("C:\!ee\AgreementBase\ee_company_details_BANANA.xls")
'Set TargetWb = Workbooks("ee_agreement_base.xls")


'set worksheets
Set TargetWs = TargetWb.Sheets("AgreementData")
Set SourceWs = SourceWb.Sheets("CompanyData")

'activate SourceWb
SourceWb.Activate

'get Company Code to process
ActiveCompany = SourceWs.Range("B41").Value
If ActiveCompany = "" Then
                  TargetWb.Close False
                  Exit Sub
End If
'ActiveCompanyRow = ActiveCell.Row

'ask whether to send this Company to db
OverWrite = MsgBox("Add data for " & ActiveCompany & " to ee_agreement_base.xls?", vbYesNo)
            If OverWrite = vbNo Then Exit Sub

'activate TargetWb
TargetWb.Activate

'get last non-empty row in TargetWs
With TargetWs
    .Select
    .Range("A1").Select
End With
LastRowAgreementDb = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

'search for the Company code in TargetWs
For i = 1 To LastRowAgreementDb
    If TargetWs.Range("A" & i).Value = ActiveCompany Then Exit For
Next
TargetCompanyRow = i

'set source and target range
Set SourceRange = SourceWs.Range("D2:D38")
Set SourceCompanyCodeRange = SourceWs.Range("B41")
Set TargetCompanyCodeRange = TargetWs.Range("A" & TargetCompanyRow)
Set TargetRange = TargetWs.Range("B" & TargetCompanyRow & ":AL" & TargetCompanyRow)

'overwrite company code in target ws
TargetCompanyCodeRange = SourceCompanyCodeRange.Value

'copy paste company data to target ws
SourceWs.Activate
SourceRange.Select
    Selection.Copy
    TargetWs.Activate
    TargetRange.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
Application.CutCopyMode = False

'get last column
TargetWs.Activate
LastColTargetWs = Cells(1, Columns.Count).End(xlToLeft).Column

'sort agreement db alphabetically
If TargetCompanyRow > LastRowAgreementDb Then
   Set SortRange = TargetWs.Range(TargetWs.Cells(2, 1), TargetWs.Cells(TargetCompanyRow, LastColTargetWs))
       Else: Set SortRange = TargetWs.Range(TargetWs.Cells(2, 1), TargetWs.Cells(LastRowAgreementDb, LastColTargetWs))
End If
   
SortRange.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo

'close SourceWb
SourceWb.Close False

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = True
Application.AutomationSecurity = msoAutomationSecurityForceEnable

End Sub

Open in new window

Comment
Watch Question

I guess because of this

If ActiveCompany = "" Then
                  TargetWb.Close False
                  Exit Sub
End If

Open in new window


I believe there is no value in Range("B41") and then it enters that loop and then exits the sub :)

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Are you running this from a shortcut key combination and does the workbook you are opening have code in its Workbook_Open event?

Author

Commented:
hm.... there is a value in B41...
I upload both workbooks here
ee-agreement-base.xls
ee-company-details-BANANA.xls
Well, I tried it and it works. I got the message which says

MsgBox("Add data for " & ActiveCompany & " to ee_agreement_base.xls?", vbYesNo)

Once thing that I notice which is different is that your file name in the code is

ee_company_details_BANANA.xls

But the Actual File name is

ee-company-details-BANANA.xls

Could that be the problem?

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Remove this line:
'Application.AutomationSecurity = msoAutomationSecurityForceDisable

Open in new window

Author

Commented:
@Sid. uploading on ee changes _ to -    locally, this is correct.

@rorya: the file I am opening (not this test file) has code in it. with this line you suggest to remove I tried to avoid that the dialogue box appears to ask whether enable or disable macros. Is there another way to do this?
Well, In that case, The macro ran fine on my pc.

Just enter this one line "MsgBox ActiveCompany" and tell me what do you see?

ActiveCompany = SourceWs.Range("B41").Value
MsgBox ActiveCompany
If ActiveCompany = "" Then
    TargetWb.Close False
    Exit Sub
End If

Open in new window


Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
unfortunately, I do not even get there (hope I did it correctly) it really exits before after this:
Set SourceWb = Workbooks.Open("C:\!ee\AgreementBase\ee_company_details_BANANA.xls")

'set and open workbooks
Set TargetWb = ActiveWorkbook
Set SourceWb = Workbooks.Open("C:\!ee\AgreementBase\ee_company_details_BANANA.xls")
'Set TargetWb = Workbooks("ee_agreement_base.xls")


'set worksheets
Set TargetWs = TargetWb.Sheets("AgreementData")
Set SourceWs = SourceWb.Sheets("CompanyData")

'activate SourceWb
SourceWb.Activate

'get Company Code to process
ActiveCompany = SourceWs.Range("B41").Value

MsgBox ("ActiveCompany")

Open in new window

Ok Try this file and then tell me what all message boxes do you see?

Sid
ee-agreement-base.xls

Author

Commented:
@rorya: Application.AutomationSecurity = msoAutomationSecurityLow

works!

So I set it to Application.AutomationSecurity = msoAutomationSecurityMedium at the end again, right?

@Sid: sorry, MsgBox(ActiveCompany) (returns correct value)
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
If you set it to Application.AutomationSecurity = msoAutomationSecurityByUI it will use the same settings as the UI. In later versions of Excel it is Low by default.
Gr8 then Rorya was bang on target :)

Sid
Quick question Rory.

When I tried the original code it worked just fine?

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Then you are using 2007 or later?
Ah! Yes you are right.

Sid

Author

Commented:
@rorya

yeah, everything works great, except that my try

Application.AutomationSecurity = msoAutomationSecurityMedium

does not work.

how can i reset it that the dialogue box appears again to ask wheter to enable or disable macros?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Use msoAutomationSecurityByUI as I mentioned earlier! :)

Author

Commented:
he, who carefully reads expert's responses, has definitely an advantage! sorry rorya! :)
thanks

Author

Commented:
first class. thanks a lot.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Glad to help. :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.