Referencing dynamic filenames

[GraciePoo]

I know nothing of visual basic, but my problem is related to it...soooo, here goes...

I set up a link such that upon clicking it, it opens an excel file. The excel file gets saved in the user's temp directory under the file's original name. The next time the person tries to open the excel file by clicking on the link, the filename changes to something else, say some vakljasfdas.xls instead of original_filename.xls.

The excel file contains macros that reference the original filename. For instance, the reference to the workbook is "original_filename.xls" However, when the filename is warped, the macro still tries to reference the original excel file, which of course, is not open. Is there a way such that I can have it reference the filename, regardless of what it is because it will always keep changing.

Thanks in advance.

 

[harmmeijer]

Maybe you should avoid using macros in the exel document wich specify a file name (and or path).
When I made some code for Access I encountered a simular problem so instead of using name and path I used currentdb.
There must be something like that in access as well.

 

[Ruairi]

ActiveWorkbook refrences the workbook that currently has the focus. Without seeing the macro code it's impossible to tell you anything more than that. If all youre doing in the macro is refrencing the workbook the macro is in there is certainly no reason to use paths. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[GraciePoo]

I tried ActiveWorkbook but it didn't work. I must be doing something wrong. Anyways, here's the code for a button in the excel sheet.

Weekly_anomaly_report.xls is the name of the excel file, but the name of course changes. What to do...what to do...


Sub InputCBNJ()
Application.ScreenUpdating = False
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Data&quot.Range("A1:az400&quot.Clear
'Workbooks.Open FileName:="E:\SYSOVHD\CBNJANM.CSV"
Workbooks.Open FileName:="\\cpp2.nj19.us-nj.citicorp.com\cpp2_t\SYSOVHD\CBNJANM.CSV"
Range("A1&quot.CurrentRegion.Copy _
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Data&quot.Range("A4&quot
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Data&quot.Range("A1&quot.Value = "CBNJ Weekly Anomaly Report"
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Graph&quot.Activate
Workbooks("CBNJANM.CSV&quot.Close
Application.ScreenUpdating = True

 

[GraciePoo]

I tried ActiveWorkbook but it didn't work. I must be doing something wrong. Anyways, here's the code for a button in the excel sheet.

Weekly_anomaly_report.xls is the name of the excel file, but the name of course changes. What to do...what to do...


Sub InputCBNJ()
Application.ScreenUpdating = False
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Data&quot.Range("A1:az400&quot.Clear
'Workbooks.Open FileName:="E:\SYSOVHD\CBNJANM.CSV"
Workbooks.Open FileName:="CBNJANM.CSV"
Range("A1&quot.CurrentRegion.Copy _
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Data&quot.Range("A4&quot
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Data&quot.Range("A1&quot.Value = "CBNJ Weekly Anomaly Report"
Workbooks("Weekly_Anomaly_Report.xls&quot.Worksheets("Graph&quot.Activate
Workbooks("CBNJANM.CSV&quot.Close
Application.ScreenUpdating = True

 

[MichaelRed]

GraciePoo,

Are ALL of the workbook names changed, or JUST ("Weekly_Anomaly_Report.xls&quot? If all of them are encrypted, I don't see how to do it without setting up and external reference. If only the one is encrypted, I don't understand why ActiveWorkbook doesn't work. Please post some detail(s) of what goes awry.

On the other hand, why do you need to encrypt the filenames? Just copy the workbooks ans run the process. As long as all of the references are in the same "Folder" the process should work.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MiggyD]

GraciePoo,

I'm not sure if I've read this posting right; but, is that macro associated to a button or a workbook?

If it's a workbook you would most likely have to keep an external database of the NEW name for referencing as MichaelRed stated above.

If it's for a button (and the code is most likely kept in a personal.xls book), you could keep the NEW file(s) referenced in the comments section--not too many users will look in there--and call (load) the required file(s).

If you want some sample code of what I'm talking about, let me know and I'll see what I can dig up for you.

--MiggyD "The world shrinks more and more with every new user online."

 

[GraciePoo]

Here is the entire code...The name of the excel file containing all of this is "This_Report.xls" Please help me out. I'm sorry if I don't pick this up quickly. Visual Basic is like a foreign language to me.



Sub InputCBNJ()
Application.ScreenUpdating = False
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1:az400&quot.Clear
'Workbooks.Open FileName:="E:\someone\CBNJANM.CSV"
Workbooks.Open FileName:="\\somewhere\something\someone\CBNJANM.CSV"
Range("A1&quot.CurrentRegion.Copy _
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A4&quot
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1&quot.Value = "CBNJ Weekly Anomaly Report"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Activate
Workbooks("CBNJANM.CSV&quot.Close
Application.ScreenUpdating = True

End Sub

Sub InputCBNY()
Application.ScreenUpdating = False
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1:az400&quot.Clear
'Workbooks.Open FileName:="E:\someone\CBNYANM.CSV"
Workbooks.Open FileName:="\\somewhere\something\someone\Cbnyanm.CSV"
Range("A1&quot.CurrentRegion.Copy _
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A4&quot
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1&quot.Value = "CBNY Weekly Anomaly Report"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Activate
Workbooks("CBNYANM.CSV&quot.Close
Application.ScreenUpdating = True
End Sub
Sub InputCBSD()
Application.ScreenUpdating = False
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1:az400&quot.Clear
'Workbooks.Open FileName:="E:\someone\CBSDANM.CSV"
Workbooks.Open FileName:="\\somewhere\something\someone\CBSDANM.CSV"
Range("A1&quot.CurrentRegion.Copy _
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A4&quot
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1&quot.Value = "CBSD Weekly Anomaly Report"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Activate
Workbooks("CBSDANM.CSV&quot.Close
Application.ScreenUpdating = True

End Sub
Sub InputCBNV()
Application.ScreenUpdating = False
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1:az400&quot.Clear
'Workbooks.Open FileName:="E:\someone\CBNVANM.CSV"
Workbooks.Open FileName:="\\somewhere\something\someone\CBNVANM.CSV"
Range("A1&quot.CurrentRegion.Copy _
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A4&quot
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1&quot.Value = "CBNV Weekly Anomaly Report"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Activate
Workbooks("CBNVANM.CSV&quot.Close
Application.ScreenUpdating = True
End Sub
Sub InputCBMD()
Application.ScreenUpdating = False
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1:az400&quot.Clear
'Workbooks.Open FileName:="E:\someone\CBNVANM.CSV"
Workbooks.Open FileName:="\\somewhere\something\someone\CBMDANM.CSV"
Range("A1&quot.CurrentRegion.Copy _
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A4&quot
Workbooks(This_Report.xls).Worksheets("Data&quot.Range("A1&quot.Value = "CBMD Weekly Anomaly Report"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Activate
Workbooks("CBMDANM.CSV&quot.Close
Application.ScreenUpdating = True

End Sub

Sub PrintReports()
Application.ScreenUpdating = False
InputCBNJ
ActiveWindow.SelectedSheets.PrintOut Copies:=1
InputCBSD
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I1&quot.Value = "Page 4"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I50&quot.Value = "Page 5"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I98&quot.Value = "Page 6"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
InputCBNV
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I1&quot.Value = "Page 7"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I50&quot.Value = "Page 8"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I98&quot.Value = "Page 9"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
InputCBNY
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I1&quot.Value = "Page 10"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I50&quot.Value = "Page 11"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I98&quot.Value = "Page 12"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
InputCBMD
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I1&quot.Value = "Page 13"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I50&quot.Value = "Page 14"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I98&quot.Value = "Page 15"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I1&quot.Value = "Page 1"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I50&quot.Value = "Page 2"
Workbooks(This_Report.xls).Worksheets("Graph&quot.Range("I98&quot.Value = "Page 3"
Application.ScreenUpdating = True
End Sub

 

[MichaelRed]

GraciePoo,

Too much for my tired old eyes and fingers to re-key. If you can send me a copy of the excel workbook and two or three sample input files, I will see if I can get it to work. It LOOKS really simplistic, but this cannnot be construed as a promise to get it fixed.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MiggyD]

Ok, first of all there's a lot of repetitive codeing here. I believe it can be reduced to about 2 or 3 SUBs, max.

Secondly, and let me see if I have this right, you have [for the sake of arguement] a "template" workbook; that, when the user activates this "template" workbook for the first?? time and exits, the "template" workbook is saved as a "NewName" book so that the user can use the "template" workbook again?

EXAMPLE:
User clicks "TempBook.xls"
user does work
user click EXIT
program saves "TempBook.xls" as "2001_Jan.xls"

(continued)
But, if the user clicks on the "new" named workbook (which has macros specifically written to use the "template&quot, you want the "New" book to contain "NewName" as the filename instead of "template". Is this analogy right?

Here's something that may help. If so, please let us know:

Sub PrintReports()
'add the red wording in the next two lines to this sub.
[red]DIM CurntBook as Variant
CurntBook=ActiveWorkbook.Name[/red] 'this is Ruairi's suggestion.

Application.ScreenUpdating = False
InputCBNJ
ActiveWindow.SelectedSheets.PrintOut Copies:=1
InputCBSD
'change all of the line(s) to have the following red word.
Workbooks([red]CurntBook[/red]).Worksheets("Graph&quot.Range("I1&quot.Value = "Page 4"
...
Application.ScreenUpdating = True

End Sub


--MiggyD "The world shrinks more and more with every new user online."

 

[GraciePoo]

Thank you MiggyD, that worked.