+ Reply to Thread
Results 1 to 9 of 9

How to stop error when I cancel the input file source command?

Hybrid View

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    Midwest, US
    MS-Off Ver
    Excel 2010
    Posts
    26

    How to stop error when I cancel the input file source command?

    Hello,

    I am using the following code to transfer battery testing data from .csv files into a .xlsm file.

    It gets what I need done but, if I select "Cancel" from the input file box it creates a Run-time error '1004': 'False.xlsx' could not be found.........

    What do I need to add to my code to stop this?

    Sub CommandButton1_Click()
    
    Dim batteryBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim batteryFilename As String
    Dim batteryWorkbook As Workbook
    Dim targetWorkbook As Workbook
    
    Set targetWorkbook = Application.ActiveWorkbook
    
    
    filter = "Text files (*.csv),*.csv"
    caption = "Please Select an input file "
    batteryFilename = Application.GetOpenFilename(filter, , caption)
    
    Set batteryWorkbook = Application.Workbooks.Open(batteryFilename)
    
    
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets(1)
    Dim sourceSheet As Worksheet
    Set sourceSheet = batteryWorkbook.Worksheets(1)
    
    targetSheet.Range("A1").Value = sourceSheet.Range("B1").Value   'Will be adding more ranges..
    
    
    batteryWorkbook.Close
    
    End Sub
    Last edited by XxCMoneyxX; 04-15-2013 at 03:23 PM.

  2. #2
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: How to stop error when I cancel the input file source command?

    If batteryFilename <> "False.xlsx" Then
         Set batteryWorkbook = Application.Workbooks.Open(batteryFilename)
    Else
         'USER CLICKED CANCEL
    End If

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    Midwest, US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: How to stop error when I cancel the input file source command?

    Thanks for the speedy reply.

    The error remains unfortunately. I am rather new in the VBA world so I posted my code again with your suggestion to make sure I am not making a rookie mistake.

    Sub CommandButton1_Click()
    
    Dim batteryBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim batteryFilename As String
    Dim batteryWorkbook As Workbook
    Dim targetWorkbook As Workbook
    
    Set targetWorkbook = Application.ActiveWorkbook
    
    
    filter = "Text files (*.csv),*.csv"
    caption = "Please Select an input file "
    batteryFilename = Application.GetOpenFilename(filter, , caption)
    
    
    
    If batteryFilename <> "False.xlsx" Then
    Set batteryWorkbook = Application.Workbooks.Open(batteryFilename)
    Else
         'USER CLIKCED CANCEL
    End If
    
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets(1)
    Dim sourceSheet As Worksheet
    Set sourceSheet = batteryWorkbook.Worksheets(1)
    
    targetSheet.Range("A1").Value = sourceSheet.Range("B1").Value   'Will be adding more ranges..
    
    
    batteryWorkbook.Close
    
    End Sub

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: How to stop error when I cancel the input file source command?

    If batteryFilename = "False" Then Exit Sub
    Set batteryWorkbook = Application.Workbooks.Open(batteryFilename)
    Last edited by shg; 04-15-2013 at 03:16 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: How to stop error when I cancel the input file source command?

    My apologies... change "False.xlsx" to just "False"

  6. #6
    Registered User
    Join Date
    11-07-2012
    Location
    Midwest, US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: How to stop error when I cancel the input file source command?

    I just tried the

    If batteryFilename <> "False" Then Exit Sub
    Set batteryWorkbook = Application.Workbooks.Open(batteryFilename)
    But that creates a 1004 error again.

  7. #7
    Registered User
    Join Date
    11-07-2012
    Location
    Midwest, US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: How to stop error when I cancel the input file source command?

    No apology needed.
    I changed it to "False" but I now am getting a Run-time error '91' Object variable or With block variable not set

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to stop error when I cancel the input file source command?

      
    If batteryFilename = "False" Then Exit Sub
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  9. #9
    Registered User
    Join Date
    11-07-2012
    Location
    Midwest, US
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: How to stop error when I cancel the input file source command?

    That did it!!

    If batteryFilename = "False" Then Exit Sub
    Thanks everyone for your input.

    Take care.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1