Runtime error 1004 - Xtreme Visual Basic Talk

06-22-2012, 04:04 AM

justinng

Newcomer

Join Date: Jun 2009

Posts: 12

Runtime error 1004

Hi all,

I have the runtime error 1004 when I am trying to run the macro in excel 2010. The marco works perfectly fine if I am using excel 2003 but not any excel newer than 2003.

The error message says Microsoft Excel cannot access to the path. But I am puzzled.

The macro looks like this:

Quote:

Option Explicit

Sub importdata()

Dim rngFullName As Range
Dim wbOpen As Workbook
Dim wsOpenPL As Worksheet
Dim wsOpenBS As Worksheet
Dim wbData As Workbook
Dim wsDataCtrl As Worksheet
Dim wsDataMP As Worksheet
Dim wsDataBS As Worksheet
Dim wsDataPL As Worksheet
Dim rngDataPL As Range
Dim rngDataBS As Range
Dim rngCell As Range
Dim strControl As String
Dim lngNext As Long
Dim rngFound As Range
Dim sb As Class1, i As Long

Application.ScreenUpdating = False

strControl = "Failed"
'setting objects on ThisWorkbook and ThisWorkbook.ActiveSheet
Set wbData = ThisWorkbook
Set wsDataCtrl = wbData.Sheets("Control")
Set wsDataMP = wbData.Sheets("Main_Page")
Set wsDataPL = wbData.Sheets("Original_CPL")
Set wsDataBS = wbData.Sheets("Original_CBS")
Set sb = New Class1 ' create a new progress bar

Set rngFullName = wsDataCtrl.Range("H5")
'if the workbboks always are located in the same directory, use
' ThisWorkbook.Path & Application.PathSeparator & Filename & ".xls"

On Error GoTo ErrorRoutine
'open workbook with data and setting objects to both Workbook and Worksheet
Set wbOpen = Workbooks.Open(rngFullName, UpdateLinks:=False)
Set wsOpenPL = wbOpen.Sheets("REPORT-PL")
Set wsOpenBS = wbOpen.Sheets("REPORT-BS")

'indicating the rows for data delivered by VLookup
Set rngDataPL = wsDataPL.Range("A13:A421").SpecialCells(xlCellTypeConstants, 23)
Set rngDataBS = wsDataBS.Range("A13:A421").SpecialCells(xlCellTypeConstants, 23)

Thank you

Best Regards,
Justin

06-22-2012, 08:23 PM

hDC_0

Contributor

* Expert *

Join Date: Feb 2004

Posts: 522

excel 2010 vba error 1004

Quote:

Originally Posted by justinng

I have the runtime error 1004 when I am trying to run the macro in excel 2010.
The error message says Microsoft Excel cannot access to the path. But I am puzzled.

This "Explanation of Trappable Errors in Visual Basic for Apps" MSDN page
offers the following description for Excel error 1004:

Quote:

[Method name] method of [object] class failed (Error 1004)

An external error occurred, such as a failure to read or write from a file.
The method cannot be used on the object.
Possible reasons include the following:

An argument contains a value that isn't valid.
A common cause of this problem is an attempt to access an object that doesn't exist
[for example, you tried to use Workbooks(5) when there were only three workbooks open].
The method cannot be used in the applied context.
For example, some Range object methods require that the range contain data; if the range doesn't contain data, the method fails.

An external error occurred, such as a failure to read or write from a file.

Looking at this code line:

Code:

' ThisWorkbook.Path & Application.PathSeparator & Filename & ".xls"

..brought to mind this StackOverFlow thread which had as an answer:

Quote:

The error is because your file extension (xls) doesn't match your file type (OpenXMLWorkbookMacroEnabled).
You would need the xlsm extension.

If your work environment utilizes SharePoint, I also found this page which recommended
this hotfix for Excel 2007 (which may also work for Excel 2010).

One of the other differences between excel 2003 and excel 2007/2010 is something call "workbook protection"
which can result in an error 1004.
This page talks about removing workbook protection.

If workbook protection proves to be the sticking point
there is also code in the above linked to thread that shows
how to use "ActiveSheet.Unprotect" in code, as follows:

If you want the worksheets to generally remain protected during use,
you could modify the code to unprotect the sheet before taking action
in code to cells/charts/other objects on it
and then re-apply the protection when that is all done. Like this:

Code:

ActiveSheet.Unprotect
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
If Range("J41") = "TIP" Then
.MinimumScale = 0.7
.MaximumScale = 1.05
Else
.MinimumScale = 0.15
.MaximumScale = 0.71
End If
End With
ActiveSheet.Protect

If the sheets are protected with a password, you have to provide that with the statements like this:

Code:

ActiveSheet.Unprotect password:="sheet password"

and

Code:

ActiveSheet.Protect password:="sheet password"

Naturally you'd replace sheet password with the actual password for the sheet.

Last edited by hDC_0; 06-22-2012 at 08:55 PM.

06-22-2012, 08:32 PM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,306

Is the workbook that you are trying to open a *.xls or an *.xlsx, *.xlsm?
Let is know on what line the error occurs, otherwise we have to resort to guessing.

__________________
No the other right mouse click

06-26-2012, 12:59 AM

justinng

Newcomer

Join Date: Jun 2009

Posts: 12

Thanks for your replies.

I have tried adding the code Const sFILTER As String = "*.xlsm, *.xlsm"but to no avail.

06-26-2012, 01:07 AM

justinng

Newcomer

Join Date: Jun 2009

Posts: 12

Thanks for your replies.

I have tried adding the code Const sFILTER As String = "*.xlsm, *.xlsm"but to no avail. I have attached the screen shot of the error massage. I guess the error was due to the file extension. Those files that I will be importing is 2003 version. How do I overcome this version so that excel 2010 will be able to read and extract the file from the path is indicated.

The file path that I pre-defined is without file extension as I understand for excel 2003, we do not have to define the file extension.

I really appreciate your help. I have not able to run the macro since I am using the latest excel version. I need to do manual copy and paste the data from 40 over workbooks into this master workbook.

Thank you

Attached Files

Screen shot for the error 1004.pdf (533.4 KB, 3 views)

06-26-2012, 08:09 PM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,306

The file extension still exists even if it is not displayed by Windows. You can Google on how to display them on your operation system version. For instance, an *.csv file can be opened by Excel and, depending upon your set-up even displays an Excel logo in the file manager/explorer window.

__________________
No the other right mouse click