Prev: How To Remove VBA Code For Distribution of an Excel Sheet
Next: Print Preview slows my code?
From: ker_01 on 27 May 2010 16:30
Some additional code that may be useful
For ii = LBound(asd) To UBound(asd)
Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil,
"\")(UBound(Split(fil, "\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil,
"\")(UBound(Split(fil, "\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = _
Array(Dir(fil), LocName, RowsOfData,
Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If


"ker_01" wrote:

> fldr = "\\share.companyname.com\folder\subfolder\"
>
> 'asd is a 1-D array of files returned in XL2003, syntax untested in XL2007
> asd = ListFiles(fldr, True)
>
> HTH,
> Keith
>
> "ryguy7272" wrote:
>
> > I'm trying to open an Excel file from SharePoint. Here is a the scenario.
> > I'd like to be able to pass a parameter from excel to SharePoint, and have
> > SharePoint identify if the file exists on my SharePoint site. For instance,
> > here is the generic URL:
> > https://collaboration.net/sites/Documents/Forms/AllItems.asp
> >
> > What I want to be able to do is pass a file name as a Parameter, and open
> > the appropriate Excel file with the same name, so it may be something like
> > this:
> > https://collaboration.net/sites/Documents/Forms/AllItems.asp?test=5262010.xls
> >
> > It will always be the URL and the word 'test' and the file name, which is
> > unique because it is an AccountID.
> >
> > I've imported from HTML sources, and aspx sources, many times, and even
> > exported from Excel to HTML, but I'm not an expert in this area, and I'm not
> > really sure how it will work with SharePoint. Any thoughts? I'd greatly
> > appreciate any input on this.
> >
> > Thanks,
> > Ryan--
> >
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
From: ker_01 on 27 May 2010 16:46
fldr = "\\share.companyname.com\folder\subfolder\"

'asd is a 1-D array of files returned in XL2003, syntax untested in XL2007
asd = ListFiles(fldr, True)

HTH,
Keith

"ryguy7272" wrote:

> I'm trying to open an Excel file from SharePoint. Here is a the scenario.
> I'd like to be able to pass a parameter from excel to SharePoint, and have
> SharePoint identify if the file exists on my SharePoint site. For instance,
> here is the generic URL:
> https://collaboration.net/sites/Documents/Forms/AllItems.asp
>
> What I want to be able to do is pass a file name as a Parameter, and open
> the appropriate Excel file with the same name, so it may be something like
> this:
> https://collaboration.net/sites/Documents/Forms/AllItems.asp?test=5262010.xls
>
> It will always be the URL and the word 'test' and the file name, which is
> unique because it is an AccountID.
>
> I've imported from HTML sources, and aspx sources, many times, and even
> exported from Excel to HTML, but I'm not an expert in this area, and I'm not
> really sure how it will work with SharePoint. Any thoughts? I'd greatly
> appreciate any input on this.
>
> Thanks,
> Ryan--
>
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
 | 
Pages: 1
Prev: How To Remove VBA Code For Distribution of an Excel Sheet
Next: Print Preview slows my code?