Free Microsoft Excel 2013 Quick Reference

Runtime error 1004 error on method Open on workbooks object

Hello:
I am a beginner in VBA for excel. I am trying to make a code that works on the workbook open event, and after looping thru a series of 10 worksheets searching for values to copy and paste in another worksheet, the code opens another workbook (with a different name), and loops thru another series of 10 worksheets that have the same name as the above mentioned searching for values to copy and paste in the same worksheet of the first open workbook. When the code tries to open the second workbook, I keep getting the runtime error 1004, error on method Open on workbooks object. Any ideas about this problem?

The code I am running is as follows:

	VB:
	
 Workbook_Open() 
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
     '  La siguiente seccion del codigo se encarga de activar la hoja2 (Equipos) y _
     '  Protegerla contra escritura, permitiendo la edicion de las celdas no protegidas _
     '  y el uso de agrupar/desagrupar datos.
     
    Hoja2.Activate 
    Range("a1").Activate 
    Hoja2.Protect Password:="", DrawingObjects:=False, Contents:=True, Scenarios:=False, _ 
    UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ 
    AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, _ 
    AllowInsertingHyperlinks:=True 
    Hoja2.EnableOutlining = True 
     
     '  La siguiente seccion del codigo se encarga de verificar la fecha de actualizacion
     '  de los listados de materias primas del archivo actual.
     
    Dim Contador_LEP As Integer 
    Dim Inicio As Integer 
    Dim Final As Integer 
    Dim Texto As String 
     
    Inicio = Sheets("Grupos de Recursos").Range("a3") 
    Final = Sheets("Grupos de Recursos").Range("a12") 
     
    For Contador_LEP = Inicio To Final 
        Texto = Sheets("Grupos de Recursos").Range("b" & 3 + Contador_LEP & "") 
        If Sheets("GR0" & Contador_LEP & " - " & Texto & "").Visible = False Then 
            Sheets("GR0" & Contador_LEP & " - " & Texto & "").Visible = True 
        Else 
        End If 
        Sheets("GR0" & Contador_LEP & " - " & Texto & "").Select 
        Range("xfd2").Activate 
        Selection.End(xlToLeft).Select 
        Selection.Copy 
        Application.Goto Reference:="EP.Fecha.GR0" & Contador_LEP & "" 
        ActiveSheet.Paste 
        Sheets("GR0" & Contador_LEP & " - " & Texto & "").Visible = False 
         
    Next Contador_LEP 
     
     ' La siguiente parte del codigo verifica cual fue la ultima fecha de actualizacion _
     ' del listado de materias primas del archivo maestro.
     
    Workbooks.Open "c:fibratorebase de datos principal fibratore.xlsm" 'Here is were I get the error
     
    For Contador_LEP = 0 To 9 
         
        Windows("Base de Datos Principal Fibratore.xlsm").Activate 
        Application.Goto Reference:="EP.Fecha.GR0" & Contador_LEP & "" 
        Selection.Copy 
        Windows("Laminados de Equipos Principales.xlsm").Activate 
        Application.Goto Reference:="EP.Fecha.GR0" & Contador_LEP & ".VMR" 
        ActiveSheet.Paste 
         
    Next Contador_LEP 
     
    Application.CutCopyMode = False 
    Windows("Base de Datos Principal Fibratore.xlsm").Activate 
    Application.ActiveWindow.Close 
     
    Sheets("Equipos").Outline.ShowLevels Rowlevels:=1 
    Application.Calculation = xlCalculationAutomatic 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The workbook that I try to open when I get the error, also runs a code on the workbook open event. The code it runs is as follows:

	VB:
	
 Workbook_Open() 
     
    Application.ScreenUpdating = False 
     
    Dim Contador_BDP As Integer 
    Dim Inicio_BDP As Integer 
    Dim Final_BDP As Integer 
    Dim Texto_BDP As String 
     
    Inicio_BDP = Sheets("Grupos de Recursos").Range("a3") 
    Final_BDP = Sheets("Grupos de Recursos").Range("a12") 
     
    For Contador_BDP = Inicio_BDP To Final_BDP 
        Texto_BDP = Sheets("Grupos de Recursos").Range("b" & 3 + Contador_BDP & "") 
        If Sheets("GR0" & Contador_BDP & " - " & Texto_BDP & "").Visible = False Then 
            Sheets("GR0" & Contador_BDP & " - " & Texto_BDP & "").Visible = True 
        Else 
        End If 
        Sheets("GR0" & Contador_BDP & " - " & Texto_BDP & "").Select 
        Range("xfd2").Activate 
        Selection.End(xlToLeft).Select 
        Selection.Copy 
        Application.Goto Reference:="EP.Fecha.GR0" & Contador_BDP & "" 
        ActiveSheet.Paste 
        Sheets("GR0" & Contador_BDP & " - " & Texto_BDP & "").Visible = False 
        ActiveWorkbook.Save 
    Next Contador_BDP 
     
    Hoja2.Outline.ShowLevels Rowlevels:=1 
    Hoja1.Activate 
    Range("a1").Activate 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,

I have an excel spreadsheet that uses a macro to open
another excel spreadsheet via Workbooks.Open(filename)

On my local drive this works fine and it opens the second
file, runs the query, and closes it without complaint.
When I run it via a network drive it works fine as well.

However, when I try to run it via a browser (i.e. put the
file on a web server and then open the url as
http://www.whatever.com/myfile.xls") and then run the
macro I get a runtime error 1004 stating "Method 'Open' of
object 'Workbooks' failed".

Any idea what is causing this?

To complicate matters more, when it gives me the error it
gives me the option of "End" or "Debug". If I click Debug
it brings up the code window. If I then click Run without
making any changes it runs properly.

Help!

Thanks,

Ken Hunter

Hi all,
I have a document with a 'consolidate' function to import data from any other excel workbooks contained within the same folder. When I run the consolidate function I get the runtime error 1004 - select method of worksheet class failed?

Interestingly I get the same error if I use 'file-open' and highlight more than one workbook?

Do you think this error is being generated because the consolidate function is trying to open multiple workbooks cuncurrently? If so, any suggestions on what to try?

I'll check out your guide on presenting code in the forum and copy it in later if it's needed.
Thanks

Hi,

Very new to VBA and having trouble with a simple macro running in 2003 that copies and pasts to another worksheet. Code as follows:

Private Sub CopyResults_Click()
'Select the filtered data and copy it
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F19").Select
' Open the template and copy in the data
Workbooks.Open Filename:="C:Athens Verification DataTemplatesVerification Template.xls"
Workbooks("Verification Template.xls").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
End Sub

When I run CopyResults, the sheet is opened and the data copied correctly, but I get a runtime error 1004 past method of worksheet class failed, with the line 'ActiveSheet.Paste' highlighted. Don't get this when I run the same macro in 2000.

Have looked for a solution online but no joy. Be grateful for any advice!

Many thanks,

Jane

Hi

Can anyone explain why I get a Excel VBA Runtime error 1004 "Select method of Range class failed" when I run the code below? Every time it runs it stops on the line shaded red below.

IAPLUS =
Workbooks("New Members.xls")

Set IAPLUSSht = IAPLUS.Sheets("Sheet1")

IAPLUS.Activate
        
IAPLUSSht.Select
        
Range("A:A").Select
        
Selection.AutoFilter
        
ActiveSheet.Range("$A$1").AutoFilter Field:=1, Criteria1:="=B120", Operator:=xlAnd

 Range("a1").Select
The only thing I can think of as to why it is causing an error is the range it is selecting has a frozen pane on the top row, would this cause a problem? I have tried changing it to A1:A10000 but it still produces an error. Can anyone help?

Thanks,

Jeskit

How are you triggering the code - if with a commandbutton, try changing the
takefocusonclick property to false - just a guess of course.

--
Regards,
Tom Ogilvy

"Jack" > wrote in message
...
> Hi,
>
> I copy and paste from a worksheet to another. It works
> well on excel2000 but not on excel97-->runtime
> error '1004' delete Method of Range Class Failed. Does it
> means the versions are not compatible?.Do you know what is
> wrong?!
>
> this is what the button has:
>
> Dim ExecComments As Worksheet
> Set ExecComments = Sheets("Comments")
>
> Dim Answers As Worksheet
> Set Answers = Sheets("Sheet1")
>
> 'delete column
> ExecComments.Columns(1).EntireColumn.Delete
>
> request = InputBox("Please enter the question number to
> see the comments (1-4)", "", "")
>
> ' Find the last row of data
> FinalRow = Answers.Range("A65536").End(xlUp).Row
>
>
> ' Loop through each row
> For X = 1 To FinalRow
>
> thisValue = Answers.Range("F" & X).Value
> If thisValue = "1" Then
> thisValueComments = Answers.Range("Z" &
> X).Value
> If Not thisValueComments = "[NULL]" Then
> Answers.Range("Z" & X).Copy
>
> NextRow = NextRow + 2
> If flag = 1 Then
> ExecComments.Range("A12").Value = "QUESTION
> 1. what team you have? "
> flag = 0
> End If
>
> ExecComments.Range("A" & NextRow).Value =
> thisValueComments
>
> End If
> End If
> Next X
>
> Thanks for your help!!!
> Jack

Has anyone an idea what I should do about the following? I have som
very simple code in Ms Excel which loads a tab-delimited file into
workbook. On my PC, this works fine. On the PC of the person for who
this project is intended, it results in a runtime error 1004, "metho
OpenText of object Workbooks failed". Our versions of Excel and Window
are identical.

MS Excel 2002, Windows 2000 professional 5.0.2195 SP4

The line which gives the runtime error is as follows:
Application.Workbooks.OpenText FileName:=vK_infile(1)
FieldInfo:=Array(Array(1, xlTextFormat)), _
DataType:=xlDelimited, Tab:=True

I've tried a lot of fiddly changes including taking out all of th
papameters to OpenText which were unnecessary, adding i
"Application.Workbooks" instead of just "Workbooks", changing vK_infil
from a string to an array of one string, and putting additional arra
elements into FieldInfo to match up with the fileds 2 to 14. Nothin
made a difference.

The text file itself is pure vanilla. And invariably, doing the impor
by hand using the dialog boxes works fine, from whoseever computer.

Any ideas?

Regards - Ke

--
Message posted from http://www.ExcelForum.com

Hi,

I have some vba coding to combine all workbooks into one, but i got an erro Runtime error 1004 , method 'copy' of object_worksheet failed. i mentioned below the sample coding. please anybody help me to solve this problem.


	VB:
	
ThisWB = ThisWorkbook.Name 
Application.EnableEvents = False 
Application.ScreenUpdating = False 
Application.DisplayAlerts = False 
path = GetDirectory 
FileName = Dir(path & "*.xls", vbNormal) 
Do Until FileName = "" 
    If FileName  ThisWB Then 
        Set Wkb = Workbooks.Open(FileName:=path & "" & FileName) 
        For Each WS In Wkb.Worksheets 
             
             
            If WorksheetFunction.CountA(WS.Cells) > 0 Then WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) 
             
        Next WS 
        Wkb.Close False 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Edit Report

I am trying to write a macro that checks a cell in one open workbook and then moves that particular sheet to another open workbook (one of three different options). The selection of the workbook does fine, I'm just having issues getting it to move the sheet. I get a Runtime error '1004': Move method of Worksheets class failed.

From all of the Google I've seen the code should work but it's erroring out on the move line. I've tried using .copy as well and it still gives me the runtime error.


	VB:
	
 
 
For i = 2 To Num_Sheets 
    Select Case Left(Workbooks("main book.xls").Sheets(i).Cells(4, 2), 2) 
         
    Case "01" 
        Workbooks(Data_Book).Sheets(i).Move After:=Workbooks(Shift1_Book).Sheets.Count 
         
    Case "02" 
         
        Workbooks(Data_Book).Sheets(i).Move After:=Workbooks(Shift2_Book).Sheets.Count 
         
    Case "03" 
         
        Workbooks(Data_Book).Sheets(i).Move After:=Workbooks(Shift3_Book).Sheets.Count 
         
    End Select 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help you can provide would be MUCH appreciated. I'm banging my head on the desk as I type...

I have an excel spreadsheet that uses a macro to open
another excel spreadsheet via Workbooks.Open(filename)

On my local drive this works fine and it opens the second
file, runs the query, and closes it without complaint.
When I run it via a network drive it works fine as well.

However, when I try to run it via a browser (i.e. put the
file on a web server and then open the url as
http://www.whatever.com/myfile.xls") and then run the
macro I get a runtime error 1004 stating "Method 'Open' of
object 'Workbooks' failed".

Any idea what is causing this?

--
Ajit

I have an excel spreadsheet that uses a macro to open
another excel spreadsheet via Workbooks.Open(filename)

On my local drive this works fine and it opens the second
file, runs the query, and closes it without complaint.
When I run it via a network drive it works fine as well.

However, when I try to run it via a browser (i.e. put the
file on a web server and then open the url as
http://www.whatever.com/myfile.xls") and then run the
macro I get a runtime error 1004 stating "Method 'Open' of
object 'Workbooks' failed".

Any idea what is causing this?

Hello guys.

I have a simple macro that basically gives format to a txt file and pastes it into an xls file.... I have sent the macro to 34 users, and 18 run it well, but the others 16 don't. In those 16 appears the runtime error 1004: AutoFill method of Range class failed


	VB:
	
 Mac1() 
     '
     ' Mac1 Macro
     '
     '
    ChDir "C:ExportaDolares" 
    Workbooks.OpenText Filename:= _ 
    "C:ExportaDolaresForeignCurrencyTxns_315_20120629.txt", Origin:=xlMSDOS, _ 
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ 
    , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _ 
    ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ 
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _ 
    TrailingMinusNumbers:=True 
    ChDir "C:USD" 
    Workbooks.Open Filename:="C:USDformato.xls", Origin:=xlWindows 
    Windows("formato.xls").Activate 
    Windows("ForeignCurrencyTxns_315_20120629.txt").Activate 
    Columns("A:M").Select 
    Columns("A:M").EntireColumn.AutoFit 
    Rows("1:1").Select 
    Selection.AutoFilter 
    ActiveSheet.Range("$A$1:$M$4680").AutoFilter Field:=1, Criteria1:="=", _ 
    Operator:=xlOr, Criteria2:="=CinemaOperator" 
    Rows("2:2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.Delete Shift:=xlUp 
    Rows("1:1").Select 
    Selection.AutoFilter 
    Columns("B:B").Select 
    Selection.Delete Shift:=xlToLeft 
    Columns("D:D").Select 
    Selection.Cut 
    Columns("B:B").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("H:I").Select 
    Selection.Cut 
    Columns("C:C").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("H:H").Select 
    Selection.Cut 
    Columns("F:F").Select 
    Selection.Insert Shift:=xlToRight 
    Columns("G:L").Select 
    Selection.Delete Shift:=xlToLeft 
    ActiveWindow.LargeScroll ToRight:=-1 
    Range("G2").Select 
    ActiveCell.FormulaR1C1 = "=DAY(RC[-5])" 
    Range("H2").Select 
    ActiveCell.FormulaR1C1 = "=MONTH(RC[-6])" 
    Range("I2").Select 
    ActiveCell.FormulaR1C1 = "=YEAR(RC[-7])" 
    Range("G2").Select 
    Lastrow = ActiveSheet.UsedRange.Rows.Count 
    Range("G2").AutoFill Destination:=Range("G2:G" & Lastrow) 
    Range("G2:G" & Lastrow).Select 
    Range("H2").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is the wrongful line:


	VB:
	
Range("G2").AutoFill Destination:=Range("G2:G" & Lastrow) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Again, I don't understand why this happens only with half the users.

Thanks in advance for your time.

ok. this is some real simple code but for some unkown reason it keeps failing on me.. does anyone have any clues or ideas as to why?


	VB:
	
Sheets("All Work").Select 
Cells.Select 
Selection.Copy 
Sheets("Sheet3").Select 
Cells.Select 
ActiveSheet.Paste 
Sheets("All Work").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The second Cells . select doesnt work for some reason when i attach it to a button on the work sheet and i get the message

"runtime error "1004"

select method of range class failed"

but when i run just the macro it all goes smooth. any help would be much appreciated.

cheers

hi

i receive an error message whenever i try to step through the
following code. i don't know why excel cannot find the cells.select ?
if anyone has an suggestions as to why, it would be greatly
appreciated.

thanks in advance - jung

i only posted a couple of lines below to get the just of the problem.
++++++++++++++++++++++++++++++
Dim lastrow As Integer

If Workbooks.Count 2 Then
Beep
MsgBox "FUNCTION CANCELLED! " + Chr$(13) + Chr$(13) & "The
Macro workbook and the workbook to be formatted should be the only
workbooks open.", vbCritical
Exit Sub
End If

ActiveWindow.ActivateNext
lastrow = Range("A65536").End(xlUp).Row ' determine last row
of data
ActiveWindow.Zoom = 85
Cells.Font.Name = "Arial"
Cells.Select 'recevie runtime error 1004
Selection.Columns.AutoFit

Range("A1").Select
ActiveCell.FormulaR1C1 = "INTERNAL #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "OBLIGOR #"

Re: http://www.excelforum.com/excel-prog...ml#post2138532, this is *exactly* the issue that I have. I think Djvice was working with the same UPC db file that I dl from the web, too. I loaded the files in the zip on that thread and got the lookup macro to work fine.

But then I tried with my data file. The dl data file "items.csv" has 1,048,571 rows of data. I renamed djvice.xls to mylookup.xls. I edited the macro to this
Sub aaa()
  Range("B:C").ClearContents
  Set cn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
  
  cn.Open "provider=microsoft.jet.oledb.4.0;data source = C:Documents and SettingsLenovo_UserMy DocumentsMy Data
Sourcesupcdirectory;extended properties = ""text; hdr=yes"""
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  
    rs.Open "select size,description from items.csv where UPC = " & ce.Value, cn, 3, 3
    Cells(ce.Row, 2).CopyFromRecordset rs
    rs.Close
  Next ce
  
  
  
  Set rs = Nothing
  Set cn = Nothing
  
End Sub
where items.csv is my data file. For the header, in the first three cells of the first row of this data file I have inserted "UPC", "size" and "description". When I run the macro I get this error message from MS VB: Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

Help, please. TIA

Hi -

Currently I have formatting macros attached to various Comboboxes and
Toggle Buttons. I copied one of the macros below. The problem I am
having now is when I try to insert cells on a different tab, I get the
Runtime Error 1004. When I click on debug, it highlights the selected
range of the macro (line 2 below). I can only make changes if I
disable the macros. Can this be fixed?

Application.ScreenUpdating = False
Worksheets("Roll-up by RSM").Range("D13:G17,I13:L17,D21:G25").Select
With Selection
If Worksheets("Master List").Range("CA26") = "% of Sales" Then
Selection.NumberFormat = "0.00%"
Else
Selection.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($*-??_);_(@_)"
End If
If Worksheets("Master List").Range("CA26") = "Total $" Then
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""_);_(@_)"
End If
End With

Thanks in advance for your help.

Stephen

I have a workbookwith 84 sheets. When I try to select (or activate) a range I
get runtime error 1004. In debugging the file, I find I can print the
individual cells (in a different workbook, wkFix), but cannot select the
range. Here's the relevant code:

With wkState.Sheets("NDX1")
For j = iCol1 To iCol2
wkFix.Sheets(1).Cells(j - iCol1 + 1, 13) = .Cells(jRow, j)
Next j

' I have no problem in printing

Range(.Cells(jRow, iCol1), .Cells(jRow, iCol2)).Select

'But the above line gives me the runtime error

End With

Can this have anything to do with the number of spreadsheets?

I'm running VBA 6.3, Excel 2003 SP1 with XP Pro

TIA,
Mike

Hi,
I'm trying to implement the following, but just couldn't figure out why
I keep getting a runtime error 1004 on Unable to set FormulaArray
Property of the Range Class. Can anyone enlighten me please? Thanks!

Dim NumRows As Integer
Dim NumBins As Integer
Dim ICol As Integer
Dim IRow As Integer
Dim sRngCol As String
Dim sRngR As String
Dim sRngC As String
Dim sRngLot As String

For IRow = 2 To NumRows
For ICol = 1 To NumBins
sRngCol = "rngCol" & ICol + 4
sRngR = "R" & IRow
sRngC = "C" & ICol
sRngLot = sRngR & sRngC
ActiveSheet.Cells(IRow, ICol).FormulaArray = _
"=AVERAGE(IF(rngCol1='" & sRngLot & ",'" & sRngCol & "))"
Next ICol
Next IRow

Workbooks.Open with CorruptLoad parameter set to xlRepairFile fails on Excel
5.0/95 file due to Chart, with Error 1004 Method 'Open' of object
'Workbooks' failed.

We have a sample Excel 5.0 (Excel 95) file that is being opened through a
Visual Basic 6.0 function on Excel 2003 COM call. The Workbooks.Open method
succeededs if CorruptLoad is set to xlNormalLoad, but it fails with Error
1004 if Corrupt Load is set to xlRepairFile.

The Excel 5.0 document has a single sheet with a Chart on it. If the file
is modified such that only the chart is removed, the Open with xlRepairFile
works. If everything else is removed from the file and the Chart remains,
the xlRepairFile Open still fails.

Any ideas on why this might be failing? Is there a limitation of Excel 2003
opening Excel 5.0 file with Charts?
Thanks for any input on this topic.

Function WorkbooksFailSafeOpen(oWorkbooks As Workbooks, strLocalInputFile As
String) As Workbook

Dim lngErrNumber As Long
Dim strErrorDescription As String

On Error Resume Next

Set WorkbooksFailSafeOpen = oWorkbooks.Open(strLocalInputFile, _
UpdateLinks:=2, _
ReadOnly:=True, _
Password:="", _
IgnoreReadOnlyRecommended:=True, _
CorruptLoad:=xlRepairFile)

lngErrNumber = Err.Number

On Error GoTo 0

' If the first open failed with Error 1004
' Method 'Open' of object Workbooks' failed
' Try the alternate Open, without xlRepairFile setting

' Note we can't seem to get the description
' "Method 'Open' of object 'Workbooks' failed"
' out of the Err.Description variable, comes out as
' "Application-defined or object-defined error"
' even though pop-up dialog displays Method 'Open'... message
' so just check against generic error number 1004

If lngErrNumber = 1004 Then
Set WorkbooksFailSafeOpen = oWorkbooks.Open(strLocalInputFile, _
UpdateLinks:=2, _
ReadOnly:=True, _
Password:="", _
IgnoreReadOnlyRecommended:=True, _
CorruptLoad:=xlNormalLoad)
End If

End Function

I've got some code that used to work and now has suddenly stopped working and I cannot see the reason why. It generates a Runtime error 1004 Application-defined or object-defined error. The line of code between the the two lines of ######## is the one highlighted when I debug. (Sorry tried highlighting line in red but couldn't get it to work!)

Anyone got any ideas?

Thanks

Martin


	VB:
	
 Button21_Click() 
     '------------------------------
     'Import products button clicked
     '------------------------------
    Dim wkbsource As Workbook 
    Dim wkbtarget As Workbook 
    Dim lfilename As String 
    Dim try As Double 
    Dim response As Double 
    Dim norows As Double 
    Dim n As Double 
    Dim productsku As Variant 
    Dim producttargetrow As Double 
    Dim norows2 As Double 
    Dim percentdone As Double 
    Dim newproducts As Double 
    Dim updatedproducts As Double 
     'first check user security level
    If Sheet23.enablesecurity.Value = True And Sheet23.allowproductaddedit = False Then 
        If currentsecuritylevel < 2 Then Call getsecuritypassword(2) 
        If currentsecuritylevel < 2 Then Exit Sub 
        oldsecuritylevel = currentsecuritylevel 'NOTE: this will only be a permanent log in
        Call updatestatusbar 
    End If 
     
    Call MsgBox("Import products data from an excel worksheet:" & vbCrLf & "" & vbCrLf & "Column A: Item Code (SKU)" & vbCrLf
& "Column B: Product Description" & vbCrLf & "Column C: Product Gross Price (inc. VAT)" & vbCrLf & "Column D: Tax Class" &
vbCrLf & "Column E: Deal Code (You may leave this column blank)" & vbCrLf & "Column F: " & Sheet18.Range("F25") & " (You may
leave this column blank)" & vbCrLf & "Column G: " & Sheet18.Range("F26") & " (You may leave this column blank)" & vbCrLf &
vbCrLf & "If the item code already exists the product details will be updated." & vbCrLf & "" & vbCrLf & "NOTE: We assume
that the first row contains column headings and not product details." & vbCrLf & "" & vbCrLf & "Please choose a file to
import.", vbInformation Or vbDefaultButton1, "Import products") 
     
    lfilename = Application.GetOpenFilename(fileFilter:="Excel Workbook (*.xlsx;*.xls), *.xlsx;*.xls") 
     'Did they cancel choosing a import file name? If so exit subroutine doing nothing
    If lfilename = "False" Then Exit Sub 
     'speed up code
    Application.ScreenUpdating = False 
    Application.EnableEvents = False 
    Application.Calculation = xlCalculationManual 
     'CHECK IF PRODUCTDATA FILE EXISTs
    Call checkproductfileexists 
     '---------------------------------------------------------------------
     'First we test the data to check that each row has data in Columns A-D
     '---------------------------------------------------------------------
    Set wkbsource = Workbooks.Open(lfilename) 
     'find number of rows in source worksheet
     
     '########################################################
     'THIS NEXT CODE LINE IS THE ONE WHICH CAUSES THE ERRORS
    norows = wkbsource.Sheets(1).Range("A" & LTrim(Str(maxrows))).End(xlUp).Row 
     '########################################################
     
    If norows < 2 Then 
         'There is no data to import
        wkbsource.Close 
        Call MsgBox("IMPORT CANCELLED: No products to import. Please check your data.", vbDefaultButton1 Or vbExclamation,
"") 
        Exit Sub 
    End If 
     
    For n = 2 To norows 'we start from row 2 as we assume that row 1 is column headings
        If n Mod 400 = 0 Then 
            Sheet12.Activate 
            percentdone = Round((n / norows) * 100, 2) 
            Sheet12.Unprotect 
            Application.ScreenUpdating = True 
            Sheet12.Cells(27, 9) = "Please Wait Testing Data..." & Str(percentdone) & "% done" 
            Application.ScreenUpdating = False 
            Sheet12.Protect 
        End If 
        If wkbsource.Sheets(1).Cells(n, 1) = vbNullString Or wkbsource.Sheets(1).Cells(n, 2) = vbNullString Or
wkbsource.Sheets(1).Cells(n, 3) = vbNullString Or wkbsource.Sheets(1).Cells(n, 4) = vbNullString Then 
             'There is missing data in columns A-D
            wkbsource.Close 
            Call MsgBox("IMPORT CANCELLED: Error on row " & Str(n) & "! There is missing data in columns A to D in the data
you want to import. Please check your data.", vbDefaultButton1 Or vbExclamation, "") 
            Sheet12.Unprotect 
            Sheet12.Cells(27, 9) = vbNullString 
            Sheet12.Protect 
            Exit Sub 
        End If 
        If Not IsNumeric(wkbsource.Sheets(1).Cells(n, 3)) Then 
             'The price column holds data that is not a number
            wkbsource.Close 
            Call MsgBox("IMPORT CANCELLED: Error on row " & Str(n) & "! Column C must only contain numbers. Please check your
data.", vbDefaultButton1 Or vbExclamation, "") 
            Sheet12.Unprotect 
            Sheet12.Cells(27, 9) = vbNullString 
            Sheet12.Protect 
            Exit Sub 
        End If 
        If wkbsource.Sheets(1).Cells(n, 1) = 0 And IsNumeric(wkbsource.Sheets(1).Cells(n, 1)) Then 
             'The item code of zero
            wkbsource.Close 
            Call MsgBox("IMPORT CANCELLED: Error on row " & Str(n) & "! Item code cannot be '0' (zero). Please check your
data.", vbDefaultButton1 Or vbExclamation, "") 
            Sheet12.Unprotect 
            Sheet12.Cells(27, 9) = vbNullString 
            Sheet12.Protect 
            Exit Sub 
        End If 
         
    Next n 
     '------------------------------------------------
     'Now import the products to the product data file
     '------------------------------------------------
     'we open the file that contains the data to import and copy this to the productdata file
    Dim test As Boolean 
beginagain3: 
    try = 0 'we try a few times to open the product data file, if we can't because other people are accessing it we will give
up
tryagain21: 
    try = try + 1 
     'now we open the product list file and check we can write to it
    Application.ScreenUpdating = False 
    test = IsFileOpen(Sheet11.Cells(18, 4) + "systemdataproductdata.xlsx") 
    If test And try > maxtry Then 
        response = MsgBox("Error! Unable to open product data file for importing stock changes to. It is probably in use by
another user. Would like to try again?", vbRetryCancel Or vbCritical Or vbDefaultButton1, "Error!") 
        If response = vbRetry Then 
            Goto beginagain3 
        Else 
            Exit Sub 
        End If 
    ElseIf test And try

Hello All - I searched the forum for a solution to this problem but could not find one.

I have a workbook (call it 'workbook1') with VBA code that opens another workbook (call it 'workbook2') from the same directory and then runs a macro from workbook2. If I open 'workbook1' first and call the macro everything works perfectly and runs as it should. IF however I have previously opened and closed 'workbook2' (without exiting excel) I get a 1004 error saying Method 'Open' of object 'Workbooks' failed. Moreover if I then try and just File>Open 'workbook2' I get the error 'Excel found unreadable content in 'workbook2.xls'. Do you want to recover....Yes No." If I choose no and exit excel and then open 'workbook2' there are no problems.

This is driving me crazy, any help, thoughts, insight, etc is much appreciated. Below is the code in workbook1 that opens workbook2.


	VB:
	
 Workbook 
Dim AlreadyOpen As Boolean 
AlreadyOpen = False 
For Each wb In Workbooks 
    If wb.Name = "workbook2.xls" Then 
        AlreadyOpen = True 
        Exit For 
    End If 
Next wb 
 
If AlreadyOpen = False Then 
    fileAddress = ThisWorkbook.Path & "workbook2.xls" 
    Workbooks.Open fileAddress, UpdateLinks:=False 
ElseIf AlreadyOpen = True Then 
    If Workbooks("workbook2.xls").ReadOnly = True Then 
        MsgBox "workbook2 is open and Read Only. Please close file.", vbOKOnly, "Read Only" 
        Exit Sub 
    End If 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi - I'm trying to trap the runtime error 1004. However, I have discovered that there are lots of different variations - these are just a few that I found this morning in my search for an answer to my question:-
- Application-defined or operation-defined error.
- Select method of range class failed.
- Unable to get the CheckBoxes property of the Workhseet class
- Method "Range' of object_Global' failed
- Copy Method of Worksheet Class failed
- Application-defined or object-defined error
- Programmatic access to the Visual Basic Project is not trusted
- Calculate method of Range class failed
- Unable to set the LineStyle property of the Border class
- Show method of Dialog class failed

I need to trap a specific variation of this error, namely:-
- Microsoft Excel cannot access the file 'bla-bla-bla'. There are several possible reasons:
• The file name or path name does not exist.
• The file you're trying to open is being used by another program. Close the document in the other program, and try again.
• The name of the workbook you're trying to save is the same as the name of another document that is read-only. Try saving the workbook with a different name.

Therefore, I need to trap an error not using Err.Number, but Err.Description. In trying to do so, I obviously cannot refer to the whole error, as this will be different each time - depending on the filename.

I've put some ErrorTrappingCode together, which is obviously not working - just wondering if anyone has any ideas that might solve this little problem for me?

Code:
ErrorTrappingCode :
    Select Case Err.Number
        Case 1004:
            Select Case Err.Description
                Case "Microsoft Excel cannot access the file":
                MsgBox "You do not have access to this drive. Please request access before trying again."
                ActiveWorkbook.Close
                Case Else
                MsgBox "Error 1004" 'for testing purposes only
                Call SEND_ERROR_EMAIL
            End Select
        Case Else:    'An error other than 1004 has occurred.
        Msg = "The following error has been sent." & vbCrLf
        ErrMsg = "Error number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description
        MsgBox Msg & vbCrLf & ErrMsg, vbCritical, "ERROR MESSAGE"
        Call SEND_ERROR_EMAIL
    End Select


I've started to get this message this morning, in a dialog box headed "Microsoft Visual Basic"

runtime error '-2147467259 (80004005)':

Method 'Add' of object 'CommandBarControls' failed

It's only doing it on my PC (I'm at work and it's not doing it on anyone else's) and only when trying to open workbook files directly from a folder by double-clicking. If I open Excel from the Start menu I get the same message, but then I can open files normally from File>Open

Sounds like a problem with Excel on my PC.....

Hi all,

The following code below is giving me this error: Method 'Open' of object 'Workbooks' failed

The script stops on the following line:

	VB:
	
, Password:="12345"[/B][/I] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have checked my local watch screen and the var strDataBase is a valid path. Additionally, the file is password protected and the password "12345" is correct.

Any ideas what the problem can be?

Thanks in advance.


	VB:
	
Sub HeadingUpdate() 
     
    Dim wbDB, wbTOOL As Workbook 
    Dim wsDB, wsTOOLmaster, wsTOOLhomepage, wsTOOLtodatabase As Worksheet 
    Dim strDataBase As String 
     
    Application.ScreenUpdating = False 
     
     'Set sheets to named sheets
    Set wbTOOL = ActiveWorkbook 
    Set wsTOOLmaster = ThisWorkbook.Sheets("MasterSheet") 
    Set wsTOOLtodatabase = ThisWorkbook.Sheets("ToDataBase") 
    Set wsTOOLhomepage = ThisWorkbook.Sheets("HomePage") 
     
     'Check that they are sure
     'If MsgBox("Are you sure you want to update DataBase headings?", _
     'vbYesNo + vbInformation, "Submit to DataBase") = vbNo Then
     '  Exit Sub
     ' End If
     
     'Set the Database location and check one has been entered!
    If wsTOOLmaster.Range("Database").Value = "" Then 
        MsgBox "You have not entered a database location!", vbOKOnly + vbCritical, "No Database Found" 
        Exit Sub 
    End If 
     
     'Else set the database
    strDataBase = wsTOOLmaster.Range("DataBase") 
     
     'Activate the todatabase sheet and copy headings
    wsTOOLtodatabase.Activate 
    Range("rngHeadings").Select 
    Range("rngHeadings").Copy 
     
     
     
     
     
     
     'Open the database to see if anyone is locked in and if someone is end sub
    [B]Workbooks.Open Filename:=strDataBase, ReadOnly:=False, Password:="12345"[/B] 
     
     
     
     
     
    Set wbDB = ActiveWorkbook 
     
    If wbDB.ReadOnly Then 
        wbDB.Close False 
        MsgBox "Database is currently opened by someone else - Please wait a few moments and try again later." 
        Exit Sub 
    End If 
     
     'Open the database and again check if the counter is still there - if not replace it
    wbDB.Activate 
    Set wsDB = Sheets("DataBase") 
    With wsDB 
        .Activate 
        .Cells(2, 1).Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
        .Cells(2, 1).Select 
    End With 
     
     'Save and close the database
    wbDB.Save 
    wbDB.Close 
     
     'Cleanup the tool and unselect ranges
    wbTOOL.Activate 
    wsTOOLtodatabase.Activate 
    Cells(1, 1).Select 
    wsTOOLhomepage.Activate 
    Cells(1, 1).Select 
     
     'MsgBox "Headings Updated."
     
    Application.CutCopyMode = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

i am trying to use active.workbook.sendmail in my macro to send a workbook.

it pops up a window that says "A program is trying to automatically send emails on your behalf.... etc."

when i reply "yes", the program runs and terminates normally. when i reply "no", "a runtime error '1004' general mail failure" occurs.

excel.exe remains in the memory and when i try to open the excel spreadsheet again, it shows "file in use" and I need to go to task manager to end the process.

tried "on error resume next" but dont seem to work.

wud greatly appreciate assistance on this matter.

my objective is to trap the error and allow excel.exe to be released from memory and the program to close normally without error.

thks/rgds
yht