How to change something in a closed workbook?

[feipezi]

Hi,
I know we can check something out in a closed workbook using the macro: GetValuesFromAClosedWorkbook. Can we go one step further by changing or modifying something in a workbook without opening it?
By VBA, I can open a workbook, modify something and save and close the book. But this is not as convenient and fast as changing something with the book closed.
Thanks in advance.

 

[firefytr]

Hello,

Sorry, no. To change values you must open it, then close it. Of course there are ways you could streamline this process, but we'd need to see what you have or know more.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[feipezi]

Hi Zack,
Thanks for the confirmation.
I have about 18 reports and each one has the size of 30 to 50 MG on average.
On each report, there are a few displayed tabs. On each tab, there are 3 to 7 ComboBoxes. Some of them are based on the named ranges updated every month. The ranges can have a few thousand rows. If the named range of current month has more rows than the month before, the drop-down list will only show as many rows as the previous month did. The extra rows will be lost. So I set up a Macro (using Range("a1").CurrentRegion) to update the list every month to make sure the same named range covers all the rows on the list.
Even so, in most the cases, the ComboBoxes may not be able to recongnize the updated range and still show fewer rows than they are supposed to. SkipVought showed me how to fix the situation but it only worked on 5-or-10-row dropdown list. It does not seem to work on the list with over a 1000 rows.
Here is Skip's code:
******************
Private Sub ComboBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ComboBox1.ListFillRange = [testrange].Address
End Sub
******************
Hope I am not boring you. I know I might ask too much to fix the situation with closed books.

Thanks again and take care.

 

[zahead31]

feipezi,

From what I gather, you need a named range that will update itself for the addition or deletion of rows of data?

What if you defined the name range with this formula?

"=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))"

This will automatically pick up any additional rows or columns in the range, and you will not need a macro to update the named range each month.

Hope this helps,
Matt

 

[firefytr]

No bother.

I do wonder though, why over 1000 rows of data in the drop down??

This sounds like you need a common place to store information, such as a server. Have you ever thought about an Access database? I'm not too versed with them, but I believe you can pass queries whilst still being closed, could be wrong there.

While I'm sure there is code we could convolute together to do exactly what you're asking, I think we need to ask the question, "Why?" Maybe there is a better way to approach what you are doing. What is the ultimate goal with this project?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[feipezi]

Hi Matt & Zack,
Thanks for your response and tips!
Here is the major part of the Macro to update the named ranges:
*********************************************
Option Base 1

Sub SetUpRangeName()
On Error Resume Next
Dim rngarray As Variant
rngarray = Array("district_name", "region_name", "territory_name", "regionlist", "distpayerlist", _
"regionramlist", "ram_code_list")
For i = LBound(rngarray) To UBound(rngarray)
Sheets(rngarray(i)).Range("a1").CurrentRegion.Name = rngarray(i)
Next
On Error GoTo 0
End Sub

Private Sub cmb_terrname_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
cmb_TerrName.ListFillRange = [territory_name].Address
End Sub
Private Sub cmbdistrictname_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
cmbDistrictName.ListFillRange = [district_name].Address
End Sub
Private Sub cmbregionname_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
cmbRegionName.ListFillRange = [region_name].Address
End Sub
Private Sub cmbterrpayer_region_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
cmbterrpayer_region.ListFillRange = [regionlist].Address
End Sub

Private Sub cmbterr_distpayername_MouseDown(ByVal Button As Variant, ByVal Shift As Variant, ByVal x As Variant, ByVal Y As Variant)
cmbterr_distpayername.ListFillRange = [distpayerlist].Address
End Sub

Sub CreateComboBoxPayer()
ActiveSheet.Shapes("cmbterr_distpayername").Delete
Dim cb6 As Object
Set cb6 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=41.25, Top:=105.75, Width:=156.75, Height:=16.5)
cb6.LinkedCell = "B7"
cb6.ListFillRange = "distpayerlist"
cb6.Name = "cmbterr_distpayername"
cb6.Object.Font.Name = "MS Sans Serif"
cb6.Object.Font.Size = 7
End Sub
*****************************************
The first Sub is to set up the named ranges, which functions similarly to Matt's Offset().
The Private Subs are based on SkipVought's idea but the last one is not working.
Therefore, I set up the last Sub to re-create the ComboBox with newly updated range: 'distpayerlist', which usually has from 500 to 2,500 rows depending on the type of the reports.
Now, what I've been working on is a production project that needs updating monthly. It's a drug report, in short, showing summary information on the sales of each region, district, territory, state and other geographical areas to all the medical insurance companies (that's why the payer list is lengthy). Each report has 30 to 70 tabs depending on the product. About 20 of the tabs are data sheets on which the displayed tabs are based. I set up a Macro to update the data sheets by replacing them with the newly created ones from a SAS (a statistical software) data source. In the updating process, I doubt the named ranges will still exist. That's why I need the Sub above-mentioned to re-create them.
Frankly, I don't want Access involved since I believe if something can be done in Excel, I don't have to resort to other applications. The involvement of Access will make the process more complicated.
Too bad I don't know how to attach a snapshot of my reports and I doubt if I can.
What really bothers me is how come the Sub 'Private Sub cmbterr_distpayername_MouseDown' is not working. If it did, I wouldn't need to re-create the ComboBox, would I?
Forgive me for being sloppy since the code above is not a decent one because the lack of declaration of the variables.
Thanks again for your help. Any suggestions will certainly be welcome.

 

[firefytr]

Why use code for dynamic names? They can be dynamic on their own. What are you defined names?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[feipezi]

I doubt names can be dynamic since when I update the sheets the new data will replace the old. Like what happened to me as I mentioned, when the rows are many, Excel won't recognize the named range. It works on the short list. I really don't know why.
What are my defined names? They are in the ARRAY() of the Sub: SetUpRangeName.
Thanks again for your time.

 

[zahead31]

Names can definitely be dynamic. I use the OFFSET() formula (listed above) all the time with pivot tables and lists. My pivot table will reference the named range, which is dynamic to grow or shrink in size if data is added or removed. I have never had to go back and update my named ranges manually or with a macro.

It seems from what you describe, these may be a solution.

 

[SkipVought]

"I doubt names can be dynamic since when I update the sheets the new data will replace the old."

Listen to Zack. The OFFSET formula a is very powerful method for maintaining Dynamic Named ranges.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[firefytr]

How exactly does your new data replace the old? You may run into problems if you use delete. If instead you use Clear you'll be much better off.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Gavona]

If you are using dynamic named ranges (eg using OFFSET) then the workbook must be open for them to be evaluated. So of less help if you are routinely accessing the ranges while the woerkbook is closed.

The workbooks must be open when you load the new data into them. Why not update the named ranges then? Maybe event driven by the process of saving the workbook.

Gavin

 

[feipezi]

Sorry guys, about the late response.
I tried OFFSET() as Matt and Zack suggested. It worked. The named range will cover all the updated data independent of their size. But the Sub I used above: SetupNamedRange() will not be dynamically working.
Here is the code that I use to process one of the reports. I don't use Delete but ClearContents on the tabs so the defined named ranges will remain existing after ClearContents.
Thanks again.

Function RegionToolUpdate(Srce, Dest, indibook, masterbook As String)
Set Srce = Workbooks(indibook).Sheets(indibook).UsedRange
Set Dest = Workbooks(masterbook).Sheets(indibook).Range("A1")
With Workbooks(masterbook)
.Sheets(indibook).Range("A1").CurrentRegion.ClearContents
Srce.Copy Dest
.Sheets(indibook).UsedRange.EntireColumn.AutoFit
End With
Workbooks(indibook).Close SaveChanges:=False
Windows(masterbook).WindowState = xlMaximized
End Function

Sub BeforeUpDate(gpath, masterbook As String)
Application.ScreenUpdating = False

Set xl = CreateObject("Excel.Sheet")
xl.Application.Workbooks.Open gpath & "Export_RegRAT_Data.xls"
xl.Application.Workbooks.Open gpath & "Export_dist_PayerData.xls"
xl.Application.Workbooks.Open gpath & "Export_terr_PayerData.xls"
xl.Application.Workbooks.Open gpath & "Export_reg_PayerData.xls"
xl.Application.Workbooks.Open gpath & "Export_RAM_Detail.xls"
xl.Application.Workbooks.Open gpath & "RATOnly_RAM_Export.xls"
xl.Application.Workbooks.Open gpath & "district_name.xls"
xl.Application.Workbooks.Open gpath & "region_name.xls"
xl.Application.Workbooks.Open gpath & "territory_name.xls"
xl.Application.Workbooks.Open gpath & "export_region_share.xls"
xl.Application.Workbooks.Open gpath & "export_payer_summary.xls"
xl.Application.Workbooks.Open gpath & "distpayerlist.xls"
xl.Application.Workbooks.Open gpath & "ram_code_list.xls"
xl.Application.Workbooks.Open gpath & "national_share_export.xls"
xl.Application.Workbooks.Open gpath & "nationalgraphdata.xls"

x = RegionToolUpdate("Srce_Regrat", "Dest_Regrat", "export_regrat_data", masterbook)
x = RegionToolUpdate("Srce_DistPay", "Dest_DistPay", "Export_Dist_PayerData", masterbook)
x = RegionToolUpdate("Srce_RegPay", "Dest_RegPay", "Export_Reg_PayerData", masterbook)
x = RegionToolUpdate("Srce_TerrPay", "Dest_TerrPay", "Export_Terr_PayerData", masterbook)
x = RegionToolUpdate("Srce_RamDtl", "Dest_RamDtl", "Export_RAM_Detail", masterbook)
x = RegionToolUpdate("Srce_OnlyRam", "Dest_OnlyRam", "RATOnly_RAM_Export", masterbook)
x = RegionToolUpdate("Srce_Dist", "Dest_Dist", "district_name", masterbook)
x = RegionToolUpdate("Srce_reg", "Dest_reg", "region_name", masterbook)
x = RegionToolUpdate("Srce_terr", "Dest_terr", "territory_name", masterbook)
x = RegionToolUpdate("Srce_regshr", "Dest_regshr", "export_region_share", masterbook)
x = RegionToolUpdate("Srce_raasum", "Dest_raasum", "export_payer_summary", masterbook)
x = RegionToolUpdate("Srce_payerlist", "Dest_payerlist", "distpayerlist", masterbook)
x = RegionToolUpdate("Srce_Ramlist", "Dest_Ramlist", "ram_code_list", masterbook)
x = RegionToolUpdate("Srce_NatShr", "Dest_NatShr", "National_Share_Export", masterbook)
x = RegionToolUpdate("Srce_NatGrShr", "Dest_NatGrShr", "NationalGraphData", masterbook)

Application.ScreenUpdating = True
End Sub

Sub doitagain()
BeforeUpDate "G:\BusUnits\BusAnalysis\CustAnalysis\Analysis Tools\Diovan_Ver2_PC_Reg\N5\", _
"Diovan_Analysis_Tool_Ver2R_Working.xls"
End Sub

Sub CloseBook()
Workbooks("udDivN5.xls").Close SaveChanges:=True
End Sub