Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Finding a file
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 337
Default Finding a file
I have the follow macro

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range


partnumber = InputBox("Please enter PART NUMBER file name to
recall", "OldTechnogologies LLC")

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"

If the file is not found I want to look in

Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber
& ".XLS

Workbooks.Open Filename:=Quote


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default Finding a file
Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range
partnumber = InputBox("Please enter PART NUMBER file name to recall",
"OldTechnogologies LLC")

'Use code here to make sure partnumber is valid

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & Partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote
Exit Sub
DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & Partnumber
& ".XLS"
Workbooks.Open Quote
End Sub

Though you will need more error checking in case the second one doesn't
exist.

HTH,
Bernie
MS Excel MVP

"Oldjay" wrote in message
...
I have the follow macro

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range


partnumber = InputBox("Please enter PART NUMBER file name to
recall", "OldTechnogologies LLC")

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"

If the file is not found I want to look in

Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber
& ".XLS

Workbooks.Open Filename:=Quote



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Finding a file
I don't have a way to check this macro here, but I think it should work...

Sub OpenPartNumberWorkbook()
Dim Quote As String
Dim PartNumber As String
Dim Servers As Variant
Servers = Array("\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\", _
"\\Server3\Database\prodscheduling\approvedparts\" )
PartNumber = InputBox("Please enter PART NUMBER file name to recall" & _
"to recall", "OldTechnogologies LLC")
For X = LBound(Servers) To UBound(Servers)
Quote = Servers(X) & PartNumber & ".xls"
If Len(Quote) Then
Workbooks.Open Filename:=Quote
Exit Sub
End If
Next
MsgBox "File not found!"
End Sub

Note: If this works, then you can add as many server paths to the Array
function call as you want or need to.

--
Rick (MVP - Excel)


"Oldjay" wrote in message
...
I have the follow macro

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range


partnumber = InputBox("Please enter PART NUMBER file name to
recall", "OldTechnogologies LLC")

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"

If the file is not found I want to look in

Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber
& ".XLS

Workbooks.Open Filename:=Quote



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 337
Default Finding a file
I didn't tell you everything. After I find and open the workbook I will be
copying information from the found file to the original open file.

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If pn = -1 Then
partnumber = InputBox("Please enter PART NUMBER file name to
recall", "Auld Technogologies LLC")
Else
partnumber = pn
End If

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote

DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber &
".XLS"
Workbooks.Open Quote

Range("C4:C33").Select
Selection.Copy
Windows(MasterSheet).Activate
Sheets("MAIN").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlFormulas

"Bernie Deitrick" wrote:

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range
partnumber = InputBox("Please enter PART NUMBER file name to recall",
"OldTechnogologies LLC")

'Use code here to make sure partnumber is valid

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & Partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote
Exit Sub
DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & Partnumber
& ".XLS"
Workbooks.Open Quote
End Sub

Though you will need more error checking in case the second one doesn't
exist.

HTH,
Bernie
MS Excel MVP

"Oldjay" wrote in message
...
I have the follow macro

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range


partnumber = InputBox("Please enter PART NUMBER file name to
recall", "OldTechnogologies LLC")

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"

If the file is not found I want to look in

Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber
& ".XLS

Workbooks.Open Filename:=Quote




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 337
Default Finding a file
The code does not work if the part number is in the PART NUMBER1 folder. It
still goes to the DoesNotExist:and produces an error because the file is not
at this location
It needs some code to jump over the DoesNotExist code and go directly to the
Range("C4:C33").Select line if it finds the workbook in the PART NUMBER1
folder

"oldjay" wrote:

I didn't tell you everything. After I find and open the workbook I will be
copying information from the found file to the original open file.

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If pn = -1 Then
partnumber = InputBox("Please enter PART NUMBER file name to
recall", "Auld Technogologies LLC")
Else
partnumber = pn
End If

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote

DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber &
".XLS"
Workbooks.Open Quote

Range("C4:C33").Select
Selection.Copy
Windows(MasterSheet).Activate
Sheets("MAIN").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlFormulas

etc

"Bernie Deitrick" wrote:

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range
partnumber = InputBox("Please enter PART NUMBER file name to recall",
"OldTechnogologies LLC")

'Use code here to make sure partnumber is valid

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & Partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote
Exit Sub
DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & Partnumber
& ".XLS"
Workbooks.Open Quote
End Sub

Though you will need more error checking in case the second one doesn't
exist.

HTH,
Bernie
MS Excel MVP

"Oldjay" wrote in message
...
I have the follow macro

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range


partnumber = InputBox("Please enter PART NUMBER file name to
recall", "OldTechnogologies LLC")

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"

If the file is not found I want to look in

Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber
& ".XLS

Workbooks.Open Filename:=Quote




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default Finding a file
You need to skip the second Quote/Open block if the first succeeds:

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber & ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote

'Success, so skip to getting data
Goto GetData:

DoesNotExist:
Quote = "C:\Delete These\Book2.xls" '"\\Server3\Database\prodscheduling\approvedparts\ " & partnumber
& ".XLS"
Workbooks.Open Quote

GetData:
Range("C4:C33").Select
Selection.Copy
Windows(MasterSheet).Activate
Sheets("MAIN").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlFormulas

HTH,
Bernie
MS Excel MVP


"oldjay" wrote in message
...
The code does not work if the part number is in the PART NUMBER1 folder. It
still goes to the DoesNotExist:and produces an error because the file is not
at this location
It needs some code to jump over the DoesNotExist code and go directly to the
Range("C4:C33").Select line if it finds the workbook in the PART NUMBER1
folder

"oldjay" wrote:

I didn't tell you everything. After I find and open the workbook I will be
copying information from the found file to the original open file.

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If pn = -1 Then
partnumber = InputBox("Please enter PART NUMBER file name to
recall", "Auld Technogologies LLC")
Else
partnumber = pn
End If

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote

DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber &
".XLS"
Workbooks.Open Quote

Range("C4:C33").Select
Selection.Copy
Windows(MasterSheet).Activate
Sheets("MAIN").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlFormulas

etc

"Bernie Deitrick" wrote:

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range
partnumber = InputBox("Please enter PART NUMBER file name to recall",
"OldTechnogologies LLC")

'Use code here to make sure partnumber is valid

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & Partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote
Exit Sub
DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & Partnumber
& ".XLS"
Workbooks.Open Quote
End Sub

Though you will need more error checking in case the second one doesn't
exist.

HTH,
Bernie
MS Excel MVP

"Oldjay" wrote in message
...
I have the follow macro

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range


partnumber = InputBox("Please enter PART NUMBER file name to
recall", "OldTechnogologies LLC")

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"

If the file is not found I want to look in

Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber
& ".XLS

Workbooks.Open Filename:=Quote






Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 337
Default Finding a file
Thanks That did the trick

"Bernie Deitrick" wrote:

You need to skip the second Quote/Open block if the first succeeds:

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber & ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote

'Success, so skip to getting data
Goto GetData:

DoesNotExist:
Quote = "C:\Delete These\Book2.xls" '"\\Server3\Database\prodscheduling\approvedparts\ " & partnumber
& ".XLS"
Workbooks.Open Quote

GetData:
Range("C4:C33").Select
Selection.Copy
Windows(MasterSheet).Activate
Sheets("MAIN").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlFormulas

HTH,
Bernie
MS Excel MVP


"oldjay" wrote in message
...
The code does not work if the part number is in the PART NUMBER1 folder. It
still goes to the DoesNotExist:and produces an error because the file is not
at this location
It needs some code to jump over the DoesNotExist code and go directly to the
Range("C4:C33").Select line if it finds the workbook in the PART NUMBER1
folder

"oldjay" wrote:

I didn't tell you everything. After I find and open the workbook I will be
copying information from the found file to the original open file.

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

If pn = -1 Then
partnumber = InputBox("Please enter PART NUMBER file name to
recall", "Auld Technogologies LLC")
Else
partnumber = pn
End If

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote

DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber &
".XLS"
Workbooks.Open Quote

Range("C4:C33").Select
Selection.Copy
Windows(MasterSheet).Activate
Sheets("MAIN").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlFormulas

etc

"Bernie Deitrick" wrote:

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range
partnumber = InputBox("Please enter PART NUMBER file name to recall",
"OldTechnogologies LLC")

'Use code here to make sure partnumber is valid

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & Partnumber
& ".XLS"
On Error GoTo DoesNotExist
Workbooks.Open Quote
Exit Sub
DoesNotExist:
Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & Partnumber
& ".XLS"
Workbooks.Open Quote
End Sub

Though you will need more error checking in case the second one doesn't
exist.

HTH,
Bernie
MS Excel MVP

"Oldjay" wrote in message
...
I have the follow macro

Sub RecallTemplatePartNumber(Optional pn As Long = -1)
Dim partbook As String
Dim partnumber As String
Dim r As Range


partnumber = InputBox("Please enter PART NUMBER file name to
recall", "OldTechnogologies LLC")

Quote = "\\SERVER3\Jobs\Estimate1\TEMPLATE1\PART NUMBER1\" & partnumber
& ".XLS"

If the file is not found I want to look in

Quote = "\\Server3\Database\prodscheduling\approvedpar ts\" & partnumber
& ".XLS

Workbooks.Open Filename:=Quote







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding of temp file KC Excel Discussion (Misc queries) 4 May 12th 09 03:10 AM
Finding a matching value in another file andim Excel Programming 0 June 30th 08 03:56 PM
Finding file DKS Excel Programming 5 August 11th 06 08:37 PM
finding a file Q Excel Discussion (Misc queries) 1 May 7th 06 10:56 AM
finding the right file lou sanderson Excel Programming 1 May 23rd 05 06:20 PM


All times are GMT +1. The time now is 06:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement