1

Pretty new to vba, but I wrote a code that unmerges rows within a column, copies thus cells then pastes within a different macro workbook. The code ran well when I first ran it. However I attempted to use it today and I was shocked I had an error. This error stated that it could not find the destination file name. which was weird because the destination file name did not change or move. here is a sample of the code.

The error shows at line:

Set book2 = Workbooks.Open("Fiscal '17 Reported Straddle Fuel Usage.xlsm")

The code is as below:

Public Sub Button6_Click()
       'Change name of button

       Worksheets("7500").Buttons("Button 6").Text = "Send"
       With Sheets("7500")

       'Unmerge cells
       .Range("AI3:AI92").Select
       With Selection
        For Each rCell In Selection
         With rCell
         If .MergeCells Then
          .MergeArea.UnMerge
         End If
        End With
       Next rCell
       End With
      End With

      'open destination workbook

       Dim book1 As Workbook
       Dim sheet1 As Worksheet
       Dim book2 As Workbook
       Dim sheet2 As Worksheet
       Application.ScreenUpdating = False
       Set book1 = ThisWorkbook
       Set book2 = Workbooks.Open("Fiscal '17 Reported Straddle Fuel Usage.xlsm")
       Set sheet1 = book1.Sheets("7500")
       Set sheet2 = book2.Sheets("Nov ‘17")
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Shelly Pig
  • 13
  • 1
  • 3
  • 3
    most likely your workbook file name or path changed . – pokemon_Man Aug 16 '17 at 19:56
  • Unrelated, but if `book1` is `ThisWorkbook`, your code would be much easier to follow if you dropped `book1` and used the already-existing `ThisWorkbook` object reference. Also, see [how to avoid Select and Activate](https://stackoverflow.com/q/10714251/1188513). ...and then, you already had a reference to `sheet1` in that `With` block... – Mathieu Guindon Aug 16 '17 at 20:03
  • Puzzles me why you access the very same worksheet from 2 different collections (`Worksheets`, and then `Sheets` on the very next line) - move the `.Buttons("Button 6").Text` assignment into that `With` block. – Mathieu Guindon Aug 16 '17 at 20:05

1 Answers1

2

the destination file name did not change or move

You're not specifying a path. Specify a path.

Set book2 = Workbooks.Open("Fiscal '17 Reported Straddle Fuel Usage.xlsm")

Const path As String = "C:\Foo\Bar\Fiscal '17 Reported Straddle Fuel Usage.xlsm"
Set book2 = Workbooks.Open(path)

Workbooks.Open works off whatever the CurDir path is, and the value of CurDir changes whenever the user is presented with an "open file" dialog and they start browsing around: a file name without a full path is unreliable.

As with many, MANY things in VBA, it's better to be explicit about it. Give it a full path.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • I'm pretty new to this, first code I've ever actually wrote. But this makes sense thank you. When I'm pasting should I use "path" as well instead of files name – Shelly Pig Aug 16 '17 at 21:01
  • @ShellyPig awesome. See [this Help Center page](https://stackoverflow.com/help/accepted-answer) if you're not sure what to do next. – Mathieu Guindon Aug 16 '17 at 21:01
  • Hadn't, but if you're not sure what's going on you need to read up about using variables and constants in VBA. – Mathieu Guindon Aug 16 '17 at 21:17
  • Thanks it worked, not sure why event handler was public. It's not even on my original code. But thanks again for the help. – Shelly Pig Aug 16 '17 at 23:30