Help with opening variable filenames
 

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/15/2006 by RADLab
'
' Keyboard Shortcut: Ctrl+m
'
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\RADLab\Desktop\Tyler\51-100\124shift_focus.diff.mea.51_100.xls"
_
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1),
Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1),
Array(26, 1), Array(27, 1), Array( _
28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1),
Array(33, 1)), _
TrailingMinusNumbers:=True
Application.Run "shift_focus.diff.mea.51_100.xls!Macro1"
Selection.Copy
Windows("shift_focus.diff.mea.51_100.xls").Activat e
ActiveSheet.Paste
End Sub

Instead of 124 I want the value to be equal to the value I select but I
don't know how to insert a variable into a string.


--
MacroProblems
------------------------------------------------------------------------
MacroProblems's Profile: http://www.excelforum.com/member.php...o&userid=31596
View this thread: http://www.excelforum.com/showthread...hreadid=512883

Help with opening variable filenames
 

Splice together three strings:


replace:
"C:\Documents and
Settings\RADLab\Desktop\Tyler\51-100\124shift_focus.diff.mea.51_100.xls"

with

"C:\Documents and Settings\RADLab\Desktop\Tyler\51-100\" & s &
"shift_focus.diff.mea.51_100.xls"

where you have already
Dim s as String
s="124"

or any other string you like.
--
Gary's Student


"MacroProblems" wrote:


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/15/2006 by RADLab
'
' Keyboard Shortcut: Ctrl+m
'
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\RADLab\Desktop\Tyler\51-100\124shift_focus.diff.mea.51_100.xls"
_
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1),
Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1),
Array(26, 1), Array(27, 1), Array( _
28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1),
Array(33, 1)), _
TrailingMinusNumbers:=True
Application.Run "shift_focus.diff.mea.51_100.xls!Macro1"
Selection.Copy
Windows("shift_focus.diff.mea.51_100.xls").Activat e
ActiveSheet.Paste
End Sub

Instead of 124 I want the value to be equal to the value I select but I
don't know how to insert a variable into a string.


--
MacroProblems
------------------------------------------------------------------------
MacroProblems's Profile: http://www.excelforum.com/member.php...o&userid=31596
View this thread: http://www.excelforum.com/showthread...hreadid=512883

Help with opening variable filenames
 

I don't think I'd use .xls with a text file, but...

dim myFilename as variant
myfilename = application.getopenfilename("Text files, *.txt")
if myfilename = false then
exit sub 'user hit cancel
end if

Workbooks.OpenText Filename:= myfilename, .....

This actually lets you pick the file you want to import.

MacroProblems wrote:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/15/2006 by RADLab
'
' Keyboard Shortcut: Ctrl+m
'
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\RADLab\Desktop\Tyler\51-100\124shift_focus.diff.mea.51_100.xls"
_
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1),
Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1),
Array(26, 1), Array(27, 1), Array( _
28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1),
Array(33, 1)), _
TrailingMinusNumbers:=True
Application.Run "shift_focus.diff.mea.51_100.xls!Macro1"
Selection.Copy
Windows("shift_focus.diff.mea.51_100.xls").Activat e
ActiveSheet.Paste
End Sub

Instead of 124 I want the value to be equal to the value I select but I
don't know how to insert a variable into a string.

--
MacroProblems
------------------------------------------------------------------------
MacroProblems's Profile: http://www.excelforum.com/member.php...o&userid=31596
View this thread: http://www.excelforum.com/showthread...hreadid=512883

--

Dave Peterson

Help with opening variable filenames
 

Try something like

sNum = "124"

Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\RADLab\Desktop\Tyler\51-100\" & sNum &
"shift_focus.diff.mea.51_100.xls"

If you want the number 124 to be entered by a user try;

sNum = InputBox("Enter number")

_


"MacroProblems" wrote:


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 2/15/2006 by RADLab
'
' Keyboard Shortcut: Ctrl+m
'
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\RADLab\Desktop\Tyler\51-100\124shift_focus.diff.mea.51_100.xls"
_
, Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1),
Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1),
Array(26, 1), Array(27, 1), Array( _
28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1),
Array(33, 1)), _
TrailingMinusNumbers:=True
Application.Run "shift_focus.diff.mea.51_100.xls!Macro1"
Selection.Copy
Windows("shift_focus.diff.mea.51_100.xls").Activat e
ActiveSheet.Paste
End Sub

Instead of 124 I want the value to be equal to the value I select but I
don't know how to insert a variable into a string.


--
MacroProblems
------------------------------------------------------------------------
MacroProblems's Profile: http://www.excelforum.com/member.php...o&userid=31596
View this thread: http://www.excelforum.com/showthread...hreadid=512883