Open and send info from one workbook to another

[DARRELLTOMANDL]

Here I have. I have a workbook that has information it which I use as a template. I add information to it, which is linked to another workbook that input the info from the first workbook that is the template. So what I do is, I goto Edit......links....change source........select my file. I would like to know if there is away to make a button with in my template that opens the other workbook inputs the information. Any help is greatly appericated.

Thanks,
Darrell

 

[BrianB]

I suggest you record a macro and come back with any queries.
Copy/Paste your code too.

 

[DARRELLTOMANDL]

Sub input_info()
'
' input_info Macro
' Macro recorded 2/19/2004 by Darrell
'

'
ChDir "\\Lltoolxp\c\WINDOWS\Personal\1GM Email Quote Sheets"
Workbooks.Open Filename:= _
"\\Lltoolxp\c\WINDOWS\Personal\1GM Email Quote Sheets\EMQS.xls", UpdateLinks _
:=0
ChDir "\\Lltoolxp\c\WINDOWS\Personal\1Internal Only Grand Blanc Quotes"
ActiveWorkbook.ChangeLink Name:= _
"\\Lltoolxp\c\WINDOWS\Personal\1Internal Only Grand Blanc Quotes\IQS_2.xls", _
NewName:= _
"\\Lltoolxp\c\WINDOWS\Personal\1Internal Only Grand Blanc Quotes\LL11038.xls", _
Type:=xlExcelLinks
ActiveWindow.Close
ActiveSheet.Shapes("Button 13").Select
Selection.Characters.Text = "Button 13"
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("S5").Select
ActiveSheet.Shapes("Button 13").Select
Application.Goto Reference:="input_info"
Selection.ShapeRange.ScaleHeight 2.67, msoFalse, msoScaleFromTopLeft
End Sub

 

[DARRELLTOMANDL]

Here is my macro, but in the "\\Lltoolxp\c\WINDOWS\Personal\1Internal Only Grand Blanc Quotes\LL11038.xls I dont want to link to LL11038.xls everytime. I would like to specify the LL?????.xls, because it in never the same file name.


Darrell

 

[DARRELLTOMANDL]

Here is my macro, but in the "\\Lltoolxp\c\WINDOWS\Personal\1Internal Only Grand Blanc Quotes\LL11038.xls I dont want to link to LL11038.xls everytime. I would like to specify the LL?????.xls, because it in never the same file name.


Darrell

 

[BrianB]

Hopefull this will do what you want. It prompts for a file name. Do not put the .xls. I have left out the Button 13 code. You can put it back if you need to, but I suggest you run the code as is first to make sure it works ok. Come back with any probs.

'--------------------------------------------------------------
Sub input_info()
    Dim MyFile As String
    Dim Folder1 As String
    Dim Folder2 As String
    '----------------------------
    MyFile = "LL"
    MyFile = InputBox("Please enter new file name", "NEW FILE", MyFile)
    If MyFile = "" Then End
    MyFile = MyFile & ".xls"
    '---------------------------------------------------------
    Folder1 = "\\Lltoolxp\c\WINDOWS\Personal\1GM Email Quote Sheets\"
    ChDir Folder1
    Workbooks.Open Filename:="EMQS.xls", UpdateLinks:=0
    '--
    Folder2 = "\\Lltoolxp\c\WINDOWS\Personal\1Internal Only Grand Blanc Quotes\"
    ChDir Folder2
    ActiveWorkbook.ChangeLink Name:=Folder2 & "IQS_2.xls", _
    NewName:=Folder2 & MyFile, Type:=xlExcelLinks
    ActiveWindow.Close
End Sub
'-------------------------------------------------------------------