Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Sacramento, California, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filename variable (excel 2000 sp 3)

    I trying to open excel files using a variable name. I used Record a Macro and came up with this Marco1().

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 1/28/2005 by Mike Lorenzo
    '
    ChDir "K:Contract Status GraphsDistribution Services"
    Workbooks.Open Filename:= _
    "K:Contract Status GraphsDistribution Services4500012043 Maxim Crane Works Contract Graphs.xls"
    Sheets("Data").Select
    range("K13").Select
    Windows("Test Auto Worksheet Updating001.xls").Activate
    Selection.Copy
    Windows("4500012043 Maxim Crane Works Contract Graphs.xls").Activate
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWorkbook.Save
    ActiveWindow.Close

    End Sub
    The above macro works however you have to type or select the file.

    I want to chose the file name from a list in the active workbook.
    The test file contains the variable file name in Range(I2), and looks like this:

    'K:Contract Status GraphsDistribution Services[4500012043 Maxim Crane Works Contract Graphs.xls]

    Here is my try at using a variable file name:

    Sub OpenContractGraphUpdate()

    'Open Closed Files And Update Invoice History
    Dim WKNameNum As Integer
    Dim WKName1 As String

    WKName1 = Cells(2, 9)
    MsgBox WKName1
    WKNameNum = Len(WKName1)
    MsgBox WKNameNum
    ChDir "K:Contract Status Graphs"
    Workbooks.Open Filename:=WKName1
    Sheets("Data").Select
    range("K13").Select
    Windows("Test Auto Worksheet Updating001.xls").Activate
    Selection.Copy
    Windows("4500012043 Maxim Crane Works Contract Graphs.xls").Activate
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWorkbook.Save
    ActiveWindow.Close

    End Sub


    When I run this marco I get a run time error 1004
    "K:Contract Status GraphsDistribution Services[4500012043 Maxim Crane Works Contract Graphs.xls].xls' count not be found.

    I suspect the .xls' at the end is a problem

    Any help would be welcome.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filename variable (excel 2000 sp 3)

    Try removing the brackets from around the file name in the cell, or have your code remove them after you get the string into WKName1.

    If that does not work, then when the error occurs, what statement is highlited if you press the Debug button in the error message box?
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •