how to avoid errors as updating a master workbook? 1

[feipezi]

Hi folks,

Hope all is well with you guys.

I'm trying to update a bunch of workbooks (reports) with new data feed, which is of production job and done by month.

The reports have more than one template (show tabs) and each has a few dropdowns, mostly combobox and buttons, nothing complicated. I set up a few Excel macros to automate the process. The problems are if I do not leave one of the combobox blank, I will always get a message saying that the spreadsheet where the combobox is residing cannot be found (Run-time error "9"). As a matter of fact, the tab is right there. So the process will certainly be interrupted. Otherwise, everything will go smoothly.

Is there any way of not to invoke events until the update process finished?

I tried something like 'Application.EnableEvents=False' and so on, but the problem cannot be solved.

Hope I made myself clear. This is the code for the combobox. Not sure if it helps a little.

Private Sub cmbBoxSelectGeoGraph_Change()
Sheets("ViewGraphs").Activate 'this is the sentence where I have 'Run-time error "9"' on; if I comment it out, the error will point to another tab that has nothing to do with the current one.
CurrSheetName = ActiveSheet.Name
Call cmdGraphRunQuery_Click
On Error Resume Next
ActiveWorkbook.Names("AcctNameRange").Delete
On Error GoTo 0
x = GeoTotAcct
Sheets("geoplanlist").Range("A2:A" & x).Name = "AcctNameRange"
Call CopyAcctName
Call cmdAcctGraphRunQuery_Click
With Sheets("ViewGraphs").OLEObjects("cmbKeyAcctGraph")
.ListFillRange = "AcctNameRange"
End With
Application.Range("A1").Select
End Sub


Thanks in advance.

 

[SkipVought]

hi,

Why ACTIVATE that sheet?

Do you have more than one workbook open? Maybe ViewGraphs is not in the ActiveWorkbook?

Odd: you SPECIFY the Sheet Name and then you assign the SAME NAME to a variable???

Sheets("ViewGraphs").Activate 
CurrSheetName = ActiveSheet.Name     'isn't the ActiveSheet Name [b]ViewGraphs[/b]???

Then you NEVER use CurrSheetName???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi Skip,

I did not write the first part of the code. You're right. No need to activate the sheet. But the fact is even I comment it out , I'll still get the same error Run-time error "9". It looks like activating the tab doesn't do much harm. Of course, I'll get rid of it later.

CurrentSheetName is 100% redundant. It wasn't even called in the Module. I forgot taking it out before send you the code.

Thanks again.

 

[SkipVought]

Again, do you have more then one workbook open?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Yes, I do have more than one workbook open. So you suspect the tab Excel cannot find because it's pointing to the other opened workbook, not the one that Excel is supposed to work on. Make sense. That being the case, you think a statement of Workbooks(masterbook).Activate at proper location will solve the problems, don't you?

Thanks again.

 

[SkipVought]

Here are the tools at your beck and call.

The workbook containing the code that you are running is the ThisWorkbook object. I almost NEVER use ActiveANYTHING.

When you open another workbook, then set a workbook object for that workbook and USE that object when referring the that or any other workbook other than ThisWorkbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,

I still have something unclear.

I have 3 types of workbooks: the master file where I want to update the tabs using the individual data workbooks (2nd type); and the workbook where the updating macros are located to control the update process (3rd type). Please check the code (3rd type). The error "9" probably comes from the underlined part of the code.

Thanks in advance


Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
Dim sTemp As String, sHldr As String
If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
sTemp = Dir(fldr & fltr)
If sTemp = "" Then
FileList = Split("No files found", "|")
Exit Function
End If
Do
sHldr = Dir
If sHldr = "" Then Exit Do
sTemp = sTemp & "|" & sHldr
Loop
FileList = Split(sTemp, "|")
End Function

Sub FormatDataTabs(master As String, fold As String)
'On Error Resume Next
Application.DisplayAlerts = False
Dim Directory As String
Dim i As Long
Dim books() As String, wkbk As Workbook, _
mbook As Workbook, tabname As Variant, tabs As Variant

Directory = "C:\jqzhang\Novstuff\Analyzers_NewAlignment\CPD\" & fold & "\"
Set mbook = Workbooks.Open(Directory & master & ".xls")

books = FileList(Directory, "*.xls")
For i = LBound(books) To UBound(books)
tabname = Split(books(i), "\")
tabs = Mid(tabname(UBound(tabname)), 1, Len(tabname(UBound(tabname))) - 4)
If tabs <> master Then
mbook.Sheets(tabs).UsedRange.ClearContents
Set wkbk = Workbooks.Open(Directory & "\" & books(i))
'MsgBox ActiveWorkbook.Name
wkbk.Sheets(tabs).UsedRange.Copy Destination:=mbook.Sheets(tabs).Range("a1")
wkbk.Close SaveChanges:=False
End If
Next i
mbook.Activate
Call FormatAllDataTabs
Call SetUpNameRange
Call SetUpRangeName
Call HideTabs
Call fixREF
Call J7
Call ReplaceREF
ChngMonth "jun-2012"
Sheets("Notes").Activate
'mbook.Close SaveChanges:=True
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

Sub doit(mmyy)
FormatDataTabs "CPD_" & mmyy & "_zr1", "zr1"
'FormatDataTabs "CPD_" & mmyy & "_zr2", "zr2"
End Sub

Sub tntn()
'On Error Resume Next
doit "Apr12"
On Error GoTo 0
End Sub

 

[SkipVought]

DEBUG on this statement and discover what value tabs contains and is it a valid sheet name in your mbook?...

mbook.Sheets(tabs).UsedRange.ClearContents

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Yes. Those tabs have valid sheet names.

Let me tell you what happened. The macro above takes the tab (only one tab in each workbook; the name of the tab will be exactly the same as the name of the individual workbooks, except the suffix, and the same as the tab name of the master book) of each workbook to replace the contents of the tabs in the master workbook (mbook). The error always comes up as one of the workbooks just opens and Excel tries to find the tab from the opened workbook that has the same value as the one from the mbook (the value in one of the comboboxes). But that tab only avails in the mbook.

Is there any way that can stop the functioning of Excel Events until the update process is over? If no Events, then everything would be fine.

Thanks again for your time.

 

[SkipVought]

Don't understand the suffix thing. Does that mean that the sheet names don't exactly correspond?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}