Help with path in VBA
 

Hi all

I have the following macro (still learning writing in code)
I would like the red highlighted path to be entered via a cell or input
box??
Please suggest something simple for me to understand. THX in advance.

Sub Transfer()
'
' Transfer Macro
' Macro recorded 31/07/2005 by Mike Colvin
'
Application.ScreenUpdating = False
Sheets("Transfer").Select
Range("B2:R38").Select
Selection.Copy

Workbooks.Open Filename:="\\Fsmel01\fireworks\6- Quality\Business
Division Quality\Technical Support\Amazon\Amanda Hicks\Call
Quality\Data\Data.xls"
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Range("A38").Select
Sheets("Transfer").Select
Range("B2:R38").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Sheets("Menu").Select
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Transfer Complete" & vbNewLine & "" & vbNewLine & "REMEMBER
TO SAVE & CLOSE"

'
End Sub


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=397221

Help with path in VBA
 

I don't see anything highlighted in red, but to substitute in the string you
could use something like:

v = Range("A1").Value
or
v = InputBox("Enter Path")

if Right(v,1) < "\" then v = v & "\"
Workbooks.Open Filename:= v & Data.xls"

--
Regards,
Tom Ogilvy


"Mikeice" wrote in
message ...

Hi all

I have the following macro (still learning writing in code)
I would like the red highlighted path to be entered via a cell or input
box??
Please suggest something simple for me to understand. THX in advance.

Sub Transfer()
'
' Transfer Macro
' Macro recorded 31/07/2005 by Mike Colvin
'
Application.ScreenUpdating = False
Sheets("Transfer").Select
Range("B2:R38").Select
Selection.Copy

Workbooks.Open Filename:="\\Fsmel01\fireworks\6- Quality\Business
Division Quality\Technical Support\Amazon\Amanda Hicks\Call
Quality\Data\Data.xls"
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Range("A38").Select
Sheets("Transfer").Select
Range("B2:R38").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Sheets("Menu").Select
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Transfer Complete" & vbNewLine & "" & vbNewLine & "REMEMBER
TO SAVE & CLOSE"

'
End Sub


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=397221

Help with path in VBA
 

If I not misunderstood you, I think you can use a variable here.

For example:

Dim strFilePath as string

strFilePath = inputbox("Enter File Path here") 'from input box

or

strFilePath = Cells(1,"B").value 'from cell value


Then use that variable in your workbook.open method:

Workbook.open Filename:=strFilePath


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=397221