Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Macros to write Links
I am attempting to write a macro that will link to a file on a server.
Several portions of the path are variables so that the user can input specific dates. The path is quite long: \\USPSPS01\E-MOMO\...\Line 1\...\[L1 21 2009.xls]1ST SHIFT'!$I$20 If I screwed up the syntax, its because I'm working from memory... Here's the problem...each time the macro runs, I get a file explorer window and I have to point to the file. The problem is, I have some 216 links that I want to write! If input the formula in the form ='\\USPS.... then I get the data without pointing, but use the macro to write the formula and I have to point to it...is there no way to do this in Excel. I know there's a way in Access, but I am not familiar with Access! Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Macros to write Links
I'm not sure I understand, but I bet that the dialog that you're seeing is excel
saying that the formula you just entered refered to a location (either workbook or worksheet) that didn't exist where you said it did. The only solution I know is to be more careful--you could even test to see if the file existed before you plop the formula into a cell. dim TestStr as string dim myPath as string dim myFileName as string mypath = "\\that long path\" 'with the trailing backslash myfilename = "l1 21 2009.xls" teststr = "" on error resume next teststr = dir(mypath & myfilename) on error goto 0 if teststr = "" then 'no file with that name exists at that location 'what should happen else 'the file exists 'plop the formula into the cell end if You could even open the file to see if there was a worksheet inside that workbook with that name. Then do the plopping of the formula into the cell. TomK76 wrote: I am attempting to write a macro that will link to a file on a server. Several portions of the path are variables so that the user can input specific dates. The path is quite long: \\USPSPS01\E-MOMO\...\Line 1\...\[L1 21 2009.xls]1ST SHIFT'!$I$20 If I screwed up the syntax, its because I'm working from memory... Here's the problem...each time the macro runs, I get a file explorer window and I have to point to the file. The problem is, I have some 216 links that I want to write! If input the formula in the form ='\\USPS.... then I get the data without pointing, but use the macro to write the formula and I have to point to it...is there no way to do this in Excel. I know there's a way in Access, but I am not familiar with Access! Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Macros to write Links
Dave,
Thanks for the reply. The long path is good, I know becuase I can manually type the formula into the cell and it works. So I try with a macro. Problem is that the files are on a server, so using a path that starts with "E:\" won't work because the server is not mapped as E: on everyon's drive, so I used the \\USPSPS01\. So I am not sure whats going on... "TomK76" wrote: I am attempting to write a macro that will link to a file on a server. Several portions of the path are variables so that the user can input specific dates. The path is quite long: \\USPSPS01\E-MOMO\...\Line 1\...\[L1 21 2009.xls]1ST SHIFT'!$I$20 If I screwed up the syntax, its because I'm working from memory... Here's the problem...each time the macro runs, I get a file explorer window and I have to point to the file. The problem is, I have some 216 links that I want to write! If input the formula in the form ='\\USPS.... then I get the data without pointing, but use the macro to write the formula and I have to point to it...is there no way to do this in Excel. I know there's a way in Access, but I am not familiar with Access! Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Macros to write Links
How long is the string that points at that UNC path? (Maybe there's a length
limit that's being broken????) But my guess is that your path is still wrong. I'd try this: Dim myPath as string dim testStr as string myPath = "\\...." 'that long path if right(mypath,1) < "\" then mypath = mypath & "\" 'make sure there's a trailing backslash end if teststr = "" on error resume next teststr = dir mypath & "nul" on error goto 0 if teststr = "" then msgbox "That path wasn't found!" else msgbox "it's ok" end if TomK76 wrote: Dave, Thanks for the reply. The long path is good, I know becuase I can manually type the formula into the cell and it works. So I try with a macro. Problem is that the files are on a server, so using a path that starts with "E:\" won't work because the server is not mapped as E: on everyon's drive, so I used the \\USPSPS01\. So I am not sure whats going on... "TomK76" wrote: I am attempting to write a macro that will link to a file on a server. Several portions of the path are variables so that the user can input specific dates. The path is quite long: \\USPSPS01\E-MOMO\...\Line 1\...\[L1 21 2009.xls]1ST SHIFT'!$I$20 If I screwed up the syntax, its because I'm working from memory... Here's the problem...each time the macro runs, I get a file explorer window and I have to point to the file. The problem is, I have some 216 links that I want to write! If input the formula in the form ='\\USPS.... then I get the data without pointing, but use the macro to write the formula and I have to point to it...is there no way to do this in Excel. I know there's a way in Access, but I am not familiar with Access! Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help to write the macros given a set of data | Excel Programming | |||
How to write macros | Excel Discussion (Misc queries) | |||
macro to write other macros? | Excel Programming | |||
Can you freely write excel macros in C# yet? | Excel Programming | |||
write error - even when no macros run | Excel Programming |