News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Excel 365 VBA Runtime error 1004

    Topic Resolution: Resolved

    This topic contains 12 replies, has 2 voices, and was last updated by  Lugh 5 months, 3 weeks ago.

    • Author
      Posts
    • #1008161 Reply

      Lugh
      AskWoody_MVP

      Running macro below from Excel-Easy, I get following error:

      Run-time error '1004':
      Excel cannot access 'SalesReports'.  The document may be read-only or encrypted.

      What I’m trying to do:
      Copy sheets from a bunch of external workbooks into a new workbook I’ve created. Each external workbook contains only one sheet—the workbook & sheet names are variable.
      The externals are in folder D:\BB\SalesReports, my new workbook is elsewhere—but same problem if I put my new workbook in the D:\BB\SalesReports folder.

      Debug highlights the line:
      Workbooks.Open (directory & fileName)

      The code:

      Sub UnitSalesPrep()
      'Copy raw sheets into Prep Workbook;
      'Raw Workbooks must be in folder D:\BB Sales Reports;
      'Folder must contain UnitSalesPrep.xlsm, run macro there.
      
      'Code from Excel-Easy;
      'https://www.excel-easy.com/vba/examples/import-sheets.html
      
      'Declare 2 String variables, a Worksheet object, an Integer variable
      Dim directory As String
      Dim fileName As String
      Dim sheet As Worksheet
      Dim total As Integer
      
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      
      'Initialize variable directory.
      'Dir function finds the first *.xl?? file in this directory.
      directory = "D:\BB\SalesReports"
      fileName = Dir(directory & "*.xl??")
      'Variable fileName now holds name of 1st Excel file in directory.
      
      'Add Do While Loop to check when Dir finds no more files.
      Do While fileName <> ""
      Loop
      Workbooks.Open (directory & fileName)
      'Import the sheets from the Excel file into UnitSalesPrep.xlsm
      'Variable total tracks total # of sheets in UnitSalesPrep.xlsm
      For Each sheet In Workbooks(fileName).Worksheets
          total = Workbooks("UnitSalesPrep.xlsm").Worksheets.Count
          Workbooks(fileName).Worksheets(sheet.Name).Copy after:=Workbooks("UnitSalesPrep.xlsm").Worksheets(total)
      Next sheet
      Workbooks(fileName).Close
      'Get other files via Dir function, with no arguments.
      fileName = Dir()
      'When no more files, Dir returns ("") & VBA leaves Do While loop.
      
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      
      End Sub

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

    • #1008531 Reply

      RetiredGeek
      AskWoody MVP

      Lugh,

      A minor adjustment of the location of your Loop statement.

      Sub UnitSalesPrep()
      'Copy raw sheets into Prep Workbook;
      'Raw Workbooks must be in folder D:\BB Sales Reports;
      'Folder must contain UnitSalesPrep.xlsm, run macro there.
      
      'Code from Excel-Easy;
      'https://www.excel-easy.com/vba/examples/import-sheets.html
      
      'Declare 2 String variables, a Worksheet object, an Integer variable
      Dim directory As String
      Dim fileName As String
      Dim srcwkb as Workbook 'Use of workbook object simplifies code
      Dim sheet As Worksheet
      Dim total As Integer
      
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      
      'Initialize variable directory.
      'Dir function finds the first *.xl?? file in this directory.
      directory = "D:\BB\SalesReports"
      fileName = Dir(directory & "*.xl??") 'Get First File Name!
      'Variable fileName now holds name of 1st Excel file in directory.
      
      'Add Do While Loop to check when Dir finds no more files.
      Do While fileName <> ""
      
        Set srcwkb = Workbooks.Open (directory & fileName)
        'Import the sheets from the Excel file into UnitSalesPrep.xlsm
        'Variable total tracks total # of sheets in UnitSalesPrep.xlsm
        
          For Each sheet In srcwkb.Worksheets
             total = Workbooks("UnitSalesPrep.xlsm").Worksheets.Count
            srcwkb.Worksheets(sheet.Name).Copy after:=Workbooks("UnitSalesPrep.xlsm").Worksheets(total)
          Next sheet
      	
         srcwkb.Close
      
         fileName = Dir() 'Get Next file, when empty no more files.
      
      Loop 'When no more files, Dir returns ("") & VBA leaves Do While loop.
      
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      
      End Sub
      

      Note: code not tested but should work. Also you could dim another workbook object for your UnitSalesPrep.xlm file!

      HTH 😎

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      1 user thanked author for this post.
    • #1009574 Reply

      Lugh
      AskWoody_MVP

      RG, your Speedy Gonzales impression is impressive!

      Didn’t work, but no error—ie nothing happened, no sheets copied.

      [Hmm, Wordfence won’t let me post, says I’m being naughty—so stripping code while trying to still be understandable
      403 Forbidden
      A potentially unsafe operation has been detected in your request to this site.]

      Stepping thru with F8, it jumps from the Do While
      to
      the Application.ScreenUpdating = True

      I tried some futzing without success—no surprise there!

      Wondering… is it ok to declare a variable after the Do While?

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

    • #1011710 Reply

      RetiredGeek
      AskWoody MVP

      Lugh,

      Ok I think I know the problem. You specify that the sheet names vary in the workbooks to be copied. Are there multiple sheets per workbook to be copied or only one?

      Sub UnitSalesPrep()
      'Copy raw sheets into Prep Workbook;
      'Raw Workbooks must be in folder D:\BB Sales Reports;
      'Folder must contain UnitSalesPrep.xlsm, run macro there.
      
      'Code from Excel-Easy;
      'https://www.excel-easy.com/vba/examples/import-sheets.html
      
      'Declare 2 String variables, a Worksheet object, an Integer variable
      Dim directory As String
      Dim fileName  As String
      Dim destwkb   As Workbook
      Dim srcwkb    As Workbook 'Use of workbook object simplifies code
      Dim sheet     As Worksheet
      Dim total     As Integer
      
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      
      Set destwkb = ActiveWorkbook
      
      'Initialize variable directory.
      'Dir function finds the first *.xl?? file in this directory.
      directory = "D:\BB\SalesReports"
      fileName = Dir(directory & "*.xl??") 'Get First File Name!
      'Variable fileName now holds name of 1st Excel file in directory.
      
      'Add Do While Loop to check when Dir finds no more files.
      Do While fileName <> ""
      
        Set srcwkb = Workbooks.Open (directory & fileName)
        'Import the sheets from the Excel file into UnitSalesPrep.xlsm
        'Variable total tracks total # of sheets in UnitSalesPrep.xlsm
        
          For Each sheet In srcwkb.Worksheets
             total = destwkb.Worksheets.Count
            srcwkb.Worksheets(sheet.Name).Copy after:=destwkb.Worksheets(total)
          Next sheet
      	
         srcwkb.Close
      
         fileName = Dir() 'Get Next file, when empty no more files.
      
      Loop 'When no more files, Dir returns ("") & VBA leaves Do While loop.
      
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      
      End Sub
      

      The line with the problem is this one:

            srcwkb.Worksheets(sheet.Name).Copy after:=destwkb.Worksheets(total)
      

      If there is a single sheet changing “sheet.name” to “sheet1” should solve the problem.
      If there are multiple sheets you’ll need another loop to work your way through the sheets in the workbook.

      Here is verified working code to copy multiple sheets form multiple files. Of course it would need some adjustments to fit your situation.

      Sub ImportSheets()
      
          Dim wkbImport   As Workbook
          Dim wkbThisBk   As Workbook
          Dim sht         As Worksheet
          Dim vFilename   As Variant
          Dim iFileCntr   As Integer
          
          Application.ScreenUpdating = False
          Application.DisplayAlerts = False
          
          Set wkbThisBk = ActiveWorkbook
          vFilename = Application.GetOpenFilename( _
                        FileFilter:="Microsoft Excel Workbooks, *.xls*; *.xml", Title:="Open Workbooks", _
                        MultiSelect:=True)
                        
      Debug.Print VarType(vFilename)
                        
          If VarType(vFilename) = 8204 Then  '*** Array (8192) + Variant Array (12)! ***
      
              For iFileCntr = 1 To UBound(vFilename)
                 Set wkbImport = Application.Workbooks.Open(Filename:=vFilename(iFileCntr))
                 For Each sht In wkbImport.Sheets
                    sht.Copy After:=wkbThisBk.Sheets("MasterFile")
                 Next sht
                 
                 wkbImport.Close savechanges:=False
                 
              Next iFileCntr
          
          Else
          
            MsgBox "No File Selected!"
            Exit Sub
              
          End If
          
          Application.ScreenUpdating = True
          Application.DisplayAlerts = True
      
      End Sub   'ImportSheets()
      

      HTH 😎

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      1 user thanked author for this post.
    • #1016974 Reply

      Lugh
      AskWoody_MVP

      Many thanks again, RG.

      Are there multiple sheets per workbook to be copied or only one?

      Just one sheet per workbook.

      I’ll try your new code(s) in a few hours and report back.

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

    • #1028855 Reply

      RetiredGeek
      AskWoody MVP

      Lugh,

      Ok, based on only one sheet per imported file here’s tested code.

      Option Explicit
      
      Sub UnitSalesPrep()
      'Copy raw sheets into Prep Workbook;
      'Raw Workbooks must be in folder D:\BB Sales Reports;
      'Folder must contain UnitSalesPrep.xlsm, run macro there.
      
      'Code from Excel-Easy;
      'https://www.excel-easy.com/vba/examples/import-sheets.html
      
      'Declare 2 String variables, a Worksheet object, an Integer variable
      Dim directory As String
      Dim fileName  As String
      Dim destwkb   As Workbook
      Dim srcwkb    As Workbook 'Use of workbook object simplifies code
      Dim sheet     As Worksheet
      
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      
      Set destwkb = ActiveWorkbook
      
      'Initialize variable directory.
      'Dir function finds the first *.xl?? file in this directory.
      directory = "N:\MergeSheets\"
      fileName = Dir(directory & "*.xl??") 'Get First File Name!
      'Variable fileName now holds name of 1st Excel file in directory.
      
      'Add Do While Loop to check when Dir finds no more files.
      Do While fileName <> ""
      
        Set srcwkb = Workbooks.Open(directory & fileName)
        'Import the sheets from the Excel file into UnitSalesPrep.xlsm
        'Variable total tracks total # of sheets in UnitSalesPrep.xlsm
        
            srcwkb.Sheets(1).Copy After:=destwkb.Sheets(destwkb.Worksheets.Count)
          
         srcwkb.Close
      
         fileName = Dir() 'Get Next file, when empty no more files.
      
      Loop 'When no more files, Dir returns ("") & VBA leaves Do While loop.
      
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      
      End Sub 'UnitSalesPrep()
      

      Don’t forget to change the value for Directory as I changed it for my test!

      HTH 😎

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      1 user thanked author for this post.
    • #1047110 Reply

      Lugh
      AskWoody_MVP

      based on only one sheet per imported file here’s tested code

      That, good sir, works! Thank you—did you know RG stands for Really Good? 🙂

      FYI:
      Even tho it did the job fine—24 sheets copied into my new workbook, all with content intact—it produced an error dialog at the end:

      Run-time error '91':
      Object variable or With block variable not set

      Debug highlights the line:

            srcwkb.Sheets(1).Copy After:=destwkb.Sheets(destwkb.Worksheets.Count)

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

    • #1075798 Reply

      RetiredGeek
      AskWoody MVP

      Lugh,  is the file with the code in the same directory as the files being imported?

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      1 user thanked author for this post.
    • #1095242 Reply

      Lugh
      AskWoody_MVP

      is the file with the code in the same directory as the files being imported?

      It wasn’t, but it can be—I’d prefer it to be, in fact. Going forward, I’ll only have 4 new files once a month to deal with, so just one folder for the work would be much cleaner.

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

    • #1101878 Reply

      RetiredGeek
      AskWoody MVP

      Lugh,

      I was thinking that if you had the code file in with the rest that was causing the error.

      How many files were in the directory? You said you imported 24 sheets were there more than 24 files in the directory?

      If you wish to place the code file in the same directory a simple IF statement checking  filename against destwkb.name will do the trick.

      Do While fileName <> ""
      
        If filename <> destwkb.name Then
        
          Set srcwkb = Workbooks.Open(directory & fileName)
          'Import the sheets from the Excel file into UnitSalesPrep.xlsm
          'Variable total tracks total # of sheets in UnitSalesPrep.xlsm
        
          srcwkb.Sheets(1).Copy After:=destwkb.Sheets(destwkb.Worksheets.Count)
          
          srcwkb.Close
      
         End If
         
         fileName = Dir() 'Get Next file, when empty no more files.
      
      Loop 'When no more files, Dir returns ("") and VBA leaves Do While loop.
      

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      2 users thanked author for this post.
    • #1121610 Reply

      Lugh
      AskWoody_MVP

      How many files were in the directory? You said you imported 24 sheets were there more than 24 files in the directory?

      No, just 24. Most were XLSX, a few XLS, and one CSV which I didn’t notice until I found one sheet missing in the new workbook—I imported that manually. Perhaps the CSV caused the error?

      If you wish to place the code file in the same directory a simple IF statement checking  filename against destwkb.name will do the trick

      Thanks again, I’ll use that.

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

    • #1128171 Reply

      RetiredGeek
      AskWoody MVP

      Lugh,

      The .csv file wouldn’t have matched the filter on the Dir() statement. I’m at a loss as to why you got the error and I did not. Would need to do debugging on your machine to figure it out.

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      1 user thanked author for this post.
    • #1146443 Reply

      Lugh
      AskWoody_MVP

      No worries RG, your code did the job perfectly. I only mentioned the error for your benefit—but I guess not every occasion can be a learning experience 🙂

      Lugh.
      ~
      Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
      i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Excel 365 VBA Runtime error 1004

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.