Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't Open file with declared variable (Windows XP)

    Can anyone tell me why I'm getting a type mismatch on the the following line:

    Open "strPath" "& s" For Input As #1

    The code is below. Thanks!!

    Sub Opentest()

    Dim strFileName As String
    Dim strPath As String
    Dim fileToopen As String
    Dim File_Names As Variant
    Dim YYYY
    Dim PP As String
    Dim s ' Excel file used to aggregate AMT values
    Dim i ' Data tab in Master Amt. file
    Dim m ' Master file used for reconcilliation
    Dim a ' Collection Sheet for reconcilliation data



    'Pass the file name
    s = "AMTableProgram_m_Test.xls"
    m = "CA_NOTES_STATUS_NEWtst_Test.xls"


    'Open the file

    Application.ScreenUpdating = True
    Application.DisplayAlerts = False

    'Clear previous pd. data in Reconcilliation file (Layout)

    a = "AMT_DATA"
    Range(Cells(1, 1), Cells(500, 4)).Clear

    ' Copy current period amortization (Principal Balance, Interest Balance _
    Loan# to Reconcilliation file)

    Worksheets("Layout").Activate

    YYYY = Cells(3, 30)
    PP = Cells(2, 30)

    strPath = "S:FASTTAdvanced Desktop AppsCapMonitoringLAPRO_vs_GL_vs_AMT_ReconReconcill iation TestingPeriod " & PP
    'Open "strPath" " & strFileName" & ".xls" For Input As #1
    Open "strPath" "& s" For Input As #1


    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't Open file with declared variable (Windows XP)

    Try
    <code>
    Open strPath & "" & s For Input As #1
    </code>
    In your code, the is outside the quotes, so it is interpreted as the integer division operator, which obviously isn't valid for strings. Moreover, strPath is a variable, so it should not be in quotes, same for s.

  3. #3
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open file with declared variable (Windows XP)

    Thank you Hans!! I've been looking at this to long. [img]/forums/images/smilies/sad.gif[/img]

  4. #4
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open file with declared variable (Windows XP)

    Hans,

    Open strPath & "" & s For Input As #1

    Once the script has loaded the statement above I want to select a sheet called "DATA", copy a range of cells and paste that into my master file. What is necessary to do that? I'm trying to work with variables to make my code more understandable but it seems to bring in a lot of different scenarios that I'm not familiar with. Hopefully I'm making some sense. I appreciate your assistance as always!!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't Open file with declared variable (Windows XP)

    The line you quote is used to open a plain text file. You cannot use it to open an Excel workbook, I should have noted the file name. To open a workbook, you use something like

    Dim wbk As Workbook
    Set wbk = Workbooks.Open(strPath & "" & s)

    You can then copy cells from this workbook:

    wbk.Worksheets("DATA").Range("A130").Copy Destination:=Workbooks("Master.xls").Worksheets("T est").Range("A1")

    Substitute the appropriate names and ranges.

  6. #6
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open file with declared variable (Windows XP)

    Thanks Hans!!

Posting Permissions

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