Macro file path to open in default laction

[Faridwahidi]

Hi,

I would like to have macro to open file in default location. The original desktop location and others may have differ path

e.g.

original PC
Workbooks.Open Filename:="C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140301.xls"
Workbooks.Open Filename:="C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140302.xls"
Workbooks.Open Filename:="C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140303.xls"
Workbooks.Open Filename:="C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140304.xls"
Workbooks.Open Filename:="C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140305.xls"

others PC (ussume network drive defaulted to Y)
Workbooks.Open Filename:="Y:\eSST Daily Uptime\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140301.xls"
Workbooks.Open Filename:="Y:\eSST Daily Uptime\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140302.xls"
Workbooks.Open Filename:="Y:\eSST Daily Uptime\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140303.xls"
Workbooks.Open Filename:="Y:\eSST Daily Uptime\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140304.xls"
Workbooks.Open Filename:="Y:\eSST Daily Uptime\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140305.xls"

my suggestion is to specify the the path in one column e.g

e.g. if sheets path file is "File Directory" in column ("A1:A5")


let the program to automatically select the file & open

Hope anybody could assist me.



Regards,
Farid.

[Olly]

To be clear: you have a list of 5 filenames that you want to open, using a macro, but you want the filepath to be a parameter stored on the worksheet. Is that correct?

Where are the filename strings stored? Will the filepath be the same for all 5 files? Will it always be 5 files being opened?

[Faridwahidi]

Hi Olly, thank you for willing to assist me,


I am really new person in creating macro program.


yes correct, filepath to be stored in the same worksheet.


1. Assume filepath stored at sheetname ("File Directory") e.g. Column (A1":A31")

2. the filepath will be same but the changes on date 20140301, 20140302, 20140303 ..., 20140331 ( until 31 march 2014)


Workbooks.Open Filename:="C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140301.xls"
'' " " '" """"
'' " " '" """"
'' " " '" """"
Workbooks.Open Filename:="C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140331.xls

3. The file will be opened to copy summary into other workbook and close.


Workbooks.Open FileName:="Y:\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140301.xls"
Range("M65000").End(xlUp).Copy

'Summary file activate'
Windows("eSST Daily Uptime Summary Mar 2014.xlsm").Activate

Sheets("Availability").Select
Range("E6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
Workbooks("ATM Availability - Branch 20140301.xls").Close

then continue until 20140331



Other desktop may have differ path location
-----------------------------------------

Workbooks.Open Filename:="Y:\eSST Daily Uptime\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\ATM Availability - Branch 20140301.xls"

C:\ATM & SST - Original Desktop
Y:\eSST Daily Uptime - others Desktop (assume network drive defaulted to Y)

if user are connected to different network, it will more challenging. my friend told me that we can use PC name or IP address, but i have no idea to that.



Regards,
Farid

[Olly]

Okay. Something like:

Please Login or Register  to view this content.

You need to specify the filepath, file prefix, file suffix, month and year. These can easily be stored on a worksheet (as we have done with the filepath) or specified in the code.

Then this will loop through each day of the specified month, open the appropriate file from the specified path, and copy the last value from column M of the first worksheet of the source file, to the next unused row in Column E of worksheet "Availability" in the target file. The source file is than closed.

Does this help?

You should really be specifying network paths in UNC format, rather than mapped drive letters - \\myserver\myfolder\ will always point to the same place, but Q:\myfolder\ could point at anything, between different users / computers.

[Faridwahidi]

Hi thanks a lot

Since all files stored at my PC office & i will update the result once back to my office this monday.

[Faridwahidi]

Hi Olly,


There is run time error while opening the file at

wbSrc = Workbooks.Open(stFileName)


I already follow your instruction: copy annd paste to A2:A5 "File Directory"

stPrefix = "ATM Availability - Branch "
stSuffix = ".xls"
iYear = 2014
iMonth = 3



Regards,
Farid

[Olly]

Change that line to

Please Login or Register  to view this content.

[Faridwahidi]

Hi Olly,

still doesn't work. I attach the files for your clarification.




regards,
Farid

[Olly]

You need to enter a file path in 'File Directory'!A1, which is currently blank. For your original PC example, it should contain:

	A
1	C:\ATM & SST\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM\

My suggestion for storing the other parameters was to store just the parameter values in range A2:A5, then refer to those ranges in code. For now, let's leave that alone, until you get the basics working.

[Faridwahidi]

Hi,

If i want to open the using others PC, what is the path to be referred?



Do i need to stored this at column("A2") (assume others network drive defaulted to "Y")

Y:\eSST Daily Uptime\eSST Daily Uptime\eSST Daily Uptime Mar 2014\ATM

how does the programme recognize the or prompt user to select the path. do i need to create inputbox and key in?

[Olly]

The code as written will look in whichever path is specified in 'File Directory'!A1 - exactly as you asked for in your original post.

Do you now have a requirement for some code to change that value?!

[Faridwahidi]

ok lets close this topic. sorry for making you more confused. i am a new person in learning excel macro.



Can i asked you one more question.

how to to use Sheets Array if i have 7 sheets to sort out. e.g. "ATM", "BRUN", "CAMB", "PAPU", "LAOS", "PAPU", "LOND"


Here is the sample of sheet ("ATM")
-----------------------------------

Dim e1 As Long,

Sheets("ATM").Select
e1 = Range("I15000").End(xlUp).ROW
Range("A2:AN" & e1).Select
ActiveWorkbook.Worksheets("ATM").sort.SortFields.Clear
ActiveWorkbook.Worksheets("ATM").sort.SortFields.Add Key:=Range("M2:M" & e1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("ATM").sort.SortFields.Add Key:=Range("N2:N" & e1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("ATM").sort
.SetRange Range("A2:AN" & e1)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("O2").FormulaR1C1 = "1"
Range("O3").FormulaR1C1 = "2"
Range("O2:O3").AutoFill Destination:=Range("O2:O" & Range("I" & Rows.Count).End(xlUp).ROW)


really appreaciate your assistance

[Olly]

Ask about worksheet arrays in a new thread, please And use CODE tags around your vba code that you post.