Excel -Run Time Error 1004 'Application Defined or Object Defined Error'

Question

I have an Excel 2003 spreadsheet ("Tune.xls") with some VBA. It always fails with error:

Run Time Error 1004 'Application Defined or Object Defined Error'

When I click debug it brings me to the following line in the code :

DataImport.Range("J10").Value = FileName 'Copy path and filename to worksheet "Import Data"

and also please look into whole code as follows:

'Ensure that all global (public) flags are initialized properly; they might have been
'changed before the user requested a data import.
   Sub_Error = False
   Changed_Model_1 = False
   Changed_Model_2 = False
   Plot_Model_1 = False
   Plot_Model_2 = False
   Updated_Model_1 = False
   Updated_Model_2 = False

'Disable screen updating to avoid flicker when graph data is modified. Puting the focus away
'from the graph to worksheet "Data Import" speeds up the execution by a factor of about 4. This trick
'is ad hoc and I cannot explain why it works, but it does...
   Application.ScreenUpdating = False
   DataImport.Select

'Open window to select data file. If no file is opened, then the variable FileName will be false.
   FileName = Application.GetOpenFilename("Text Files (*.txt; *.dat),*.txt;*.dat, Excel Files (*.xls),*.xls", , "Open Process Data File")
   If FileName = False Then Exit Sub     'exit if no file was opened or cancel button selected

   ******DataImport.Range("J10").Value = FileName  'Copy path and filename to worksheet "Import Data"******

'Open data file as a workbook and use shortcut name "DataSheet" for worksheet containing data.
   Workbooks.Open FileName:=FileName, updateLinks:=0        'does not update linked cells
   Set DataSheet = ActiveWorkbook.ActiveSheet
   Set DataBook = ActiveWorkbook                            'use shortcut for Data workbook

'The data workbook might contain cells referencing a DDE link. This makes it hard to manipulate
'the cells. Therefore, the entire worksheet is copied and only the values are pasted back. This
'will essentially remove all the DDE references.
   DataSheet.Cells.Select                      'This selects all cells
   Selection.Copy
   Selection.PasteSpecial Paste:=xlValues

Please can somebody help me to get this working again?

I would start with using Option Explicit and declaring your variables properly. This combination cures half the issues — Zac
What is DataImport? And please please please declare all your variables with their types. For example, I would expect FileName to be a string but I see If FileName = False — QHarr
@kinshore:Welcome to Stack Overflow: Please read How to ask a good question, then edit your question and be sure to ask a good, clear, concise question, include the code, expected behaviour, and what is wrong... then we can try to help — Our Man in Bananas

Vityata Vityata · Accepted Answer · 2018-07-24T14:50:58

1004 is an error which can come because of various reasons. In general, it may be because:

some of the objects are not defined correctly;
the worksheet is locked;

Thus, to check the first option write these two lines before the error:

MsgBox Filename
MsgBox DataImport.Name

and see what you get. You should get something for Filename (not an error) and the DataImport.Name should be the name of the sheet.

Concerning the second option - check whether the cells in DataImport are not locked.

Excel -Run Time Error 1004 'Application Defined or Object Defined Error'

1 Answers