I am trying to copy the data of a specific sheet name in Excel that is not open in VBA and paste it into another workbook.Error message
Run-time error '1004' Unable to access '.xls'. The file is corrupted, on a non-responsive server, or set to read-only.
Applicable source code
(The Excel is not damaged, is on the desktop, and is not read-only)
Dim Path As String Dim buf As String Dim fso As FileSystemObject Set fso = New FileSystemObject Dim number As String Dim type (200) As String Dim grade (200) As String Dim width (200) As Integer Dim Height (200) As Integer Dim Length (200) As Integer Dim n As Integer Dim x As Integer Dim i As Integer Dim strDir As String Dim strFile As String Dim xls As New Excel.Application Dim wb As Workbook 'Get path With Application.FileDialog (msoFileDialogFolderPicker) .Show Path = .SelectedItems (1) End With 'Get file name buf = Dir (Path&"\ *. xls *") Number = fso.GetBaseName (Path) Debug.Print (s) Dim objCn As New ADODB.Connection Dim objRS As ADODB.Recordset Dim sSheet As String With objCn .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties ("Extended Properties") = "Excel 8.0" .Open Path&"\"&buf Set objRS = .OpenSchema (ADODB.adSchemaTables) End With Do Until objRS.EOF sSheet = objRS.Fields ("TABLE_NAME") If sSheet = "'group-1 $'" Then GoTo Group 1 ElseIf sSheet = "'Group-2 $'" Then GoTo Group 2 Else GoTo Next End If Next: objRS.MoveNext Loop Group 1: Application.ScreenUpdating = False strDir = Path&"\"&buf strFile = Dir (strDir) n = 200 x = Cells (Rows.Count, 3) .End (xlUp) .Row + 1 Set wb = xls.Workbooks.Open (strDir) ← Stop here For i = 7 To n Step 1 Type (i) = wb.Worksheets ("Group-1"). Cells (i, 3) Grade (i) = wb.Worksheets ("Group-1"). Cells (i, 4) Width (i) = wb.Worksheets ("Group-1"). Cells (i, 5) Height (i) = wb.Worksheets ("Group-1"). Cells (i, 6) Length (i) = wb.Worksheets ("Group-1"). Cells (i, 14) Cells (x, 2) = number Cells (x, 3) = Type (i) Cells (x, 4) = Grade (i) Cells (x, 5) = width (i) Cells (x, 6) = height (i) Cells (x, 7) = length (i) x = x + 1 End If Next i Set xls = Nothing Application.ScreenUpdating = True Group 2: ...
I searched online for a few days, but I don't understand the cause.
I am a VBA beginner and lack of knowledge, but I would like to ask you to use it to improve my work. Thank you.
Answer # 1
The xls file is open with ADO and you are trying to open it with Excel.Application.
If you want to process the sheets in order, you can use
For Each Item In Workbook.Worksheetsetc. without using ADO.
- solution for vba runtime error 13
- vba - i get "runtime error 424 object required"
- vba "run-time error '1004'" error that cannot be copied
- error in ado update in access vba
- vba - i get a run-time error with a macro that automatically records the date that it was entered
- i get a runtime error in c++ runtime error: reference binding to null pointer of type'int'
- vba - how to solve 91 error in find method
- an error occurs when pasting the value of the vba pastespecial method
- vba - i want to display a specified form in the access subform
- vba - how to eliminate the error that occurs when the find method is repeated in for
- vba - about cascading update of access field
- mysql 1142 error i get an access violation, so i want to grant permission
- how to resolve python exe for windows, run-time error, could not be extracted
- error when specifying cell of another workbook in vba
- xcode - unable to install "app name" error occurs
- access addition query vba to auto-near type field
- c++ - i get a runtime error i want to get the longest common string from a vector containing 3 strings
- vba - compile error: the constant is not defined
- vba - access dialog box
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- android studio - unresolved reference comes out in kotlin
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- python - importerror: cannot import name md5 error cannot be resolved