Part ONE: We have approximately 3000 reports using objects common to all users. For just these 3000 reports, we want those common objects to point to a different place. We don’t want to affect anyone else. How can we do this?
Part TWO: One suggestion is to copy the universe into another universe in the same repository, change the objects in the new universe and then manually change the 3000 reports to use the new universe. These reports aren’t published. I know how to change each report, one by one, to point to a different universe. What other ways are available to me?
I did search the archives and if the answer is there, I didn’t understand it.
Can you just change the universe so that the object is pointing to the different, correct place? If you can, don’t forget you can’t delete and re-add or your reports will break. Just edit the current object.
There’s a macro floating out there somewhere to do it… I’ll see if I can find it and post it for you…
We don’t want the object to change for any other report. For all other reports, we want the object to still point to the old location. Is there any way to do this other than what’s outlined in my Part TWO?
Ahh… I understand now. You might could write a macro to do it… speaking of…
Public UnvList As Universes
Sub ChangeUniverse()
Dim UnvCount As Integer
Dim UnvNameList() As String
Dim ix As Integer
Dim DefaultPath As String
DefaultPath = "C:\Folder_with_BODocuments"
Application.Interactive = False
Set CurDoc = Application.ActiveDocument
Set UnvList = Application.Universes
UnvCount = UnvList.Count
ReDim UnvNameList(UnvCount)
For ix = 1 To UnvCount
UnvNameList(ix) = UnvList.Item(ix).LongName
frmChoose.cboxFrom.AddItem (UnvNameList(ix))
frmChoose.cboxTo.AddItem (UnvNameList(ix))
Next ix
frmChoose.txtPath.Value = DefaultPath
Application.Interactive = True
frmChoose.cboxFrom.ListIndex = 0
frmChoose.cboxTo.ListIndex = 0
frmChoose.Show
End Sub
I didn’t write this… and I can’t remember who I got it from (so claim it if it’s yours… :)). I think what you will want to do is put all the reports you want to change in the same folder and then set that folder as the default path in the code…
Ok, I understand about putting all the files into one folder (they are already there, so that makes it easy).
So ignorant question number THREE: How do I run this macro? I’ve never had to do a macro in BusObj before. Number FOUR: Where do I specify the new universe name?
Hmm… I just tried to run it quickly and it didn’t work… I can take a look at it later tonight, but if anyone else has similar code that can help you out faster, that’s fabulous. I KNOW a very active poster sent this to me…
Anyway, I’ll try to work it out and let you know later…
Is this something that we can post in our “code library”? If so, please send a copy to Bob at busobj@forumtopics.com and I will post it. If your form is very complex, we may just have to post a link to a downloadable item, but it would be great if we can post the actual code for others to be able to use.
Karen: if we can’t get Jean-Francois’ code posted, the macro above can be rewritten so that you simply code the universe name into the macro code itself. That would be less flexible, but it can be done with a minimal amount of fuss.
HI Jean-Francois: Thank you so much for the reply! I guess I’m secretive by nature (reading too many mysteries) so I hid my email address. Now it’s not hidden.
This will save me from hours of point-and-click conversion!
Thanks to all! I obtained the relevant files from Jean-Francois and I was able to run it in my test environment. It did exactly what I needed!
I have a question related to this. Pretend I’m not using the macro:
The universe is changed, the SQL reflects the changes, and the report is refreshed. Everything works fine!
If I change the universe without refreshing the report, will the SQL still be updated to reflect any changes in the new universe? I assume so because when I do change it manually, and check the dp, the SQL has been updated. Or is it updated BECAUSE I check the dp?
The reason I’m asking is because we really just want the SQL updated and we don’t need the report to be refreshed. The reports themselves will be refreshed as needed.
The SQL is changed because you check the DP. If you just hit Refresh, it will run the SQL as it was last generated, against the connection that was last used. So, changes to either are not picked up until you enter the DP and close the Query Panel.
I could use this code as well for a current project! Could someone send to me via e-mail? Or - if it will be added to the code library shortly - I can wait till then…from all the info on this forum - I am sure Dave is busy and I don’t want to seem impatient!
(BOB member since 2002-07-17)
So ignorant question number THREE: How do I run this macro? I’ve never had to do a macro in BusObj before. Number FOUR: Where do I specify the new universe name?
