Open and send info from one workbook to another

DARRELLTOMANDL

New Member
Joined
Nov 19, 2003
Messages
36
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
I suggest you record a macro and come back with any queries.
Copy/Paste your code too.
 

DARRELLTOMANDL

New Member
Joined
Nov 19, 2003
Messages
36
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

New Member
Joined
Nov 19, 2003
Messages
36
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

New Member
Joined
Nov 19, 2003
Messages
36
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.

Code:
'--------------------------------------------------------------
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
'-------------------------------------------------------------------
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,530
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top