Application-defined or object -defined error

  • Thread starter philwants2bgolf
  • Start date
P

philwants2bgolf

I get the error messege below when I run my macro. I can't figure ou
what is wrong that causes this to happen. It breaks a
Columns("A:A").Select I have placed a comment field around it to hel
make it stand out. Any help would be great.

By the way, my two file names are 1.txt and 2.txt


Error Messege:

Run-Time error'1004':

Application-defined or object -defined error





Code
-------------------

Private Sub cmdImport_Click()
Dim schfile As Variant 'Scheduled input file with path
Dim procfile As Variant 'Case Procedure input file with path
Dim mainf As Variant 'Main program window name
Dim newf As Variant 'New file
Dim schwb As Variant 'Schedule Workbook file name
Dim prowb As Variant 'Case Procedure Workbook file name



mainf = ActiveWorkbook.Name

schfile = Application.GetOpenFilename(, , "Select Scheduled File")
If schfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

procfile = Application.GetOpenFilename(, , "Select Case Procedure File")
If procfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

Workbooks.Add
newf = ActiveWorkbook.Name

Workbooks.OpenText Filename:=schfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(20, 2), Array(31, 2), Array(72, 2))

schwb = ActiveWorkbook.Name
Workbooks.OpenText Filename:=procfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(19, 2), Array(26, 2), Array(68, 2), Array(79, 2))

procwb = ActiveWorkbook.Name


' copy and paste into one file
'
Windows(schwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("1").Select
Sheets("1").Move Before:=Workbooks(newf).Sheets(1)
Windows(procwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("2").Select
Sheets("2").Move Before:=Workbooks(newf).Sheets(2)
Sheets("Sheet1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: Sheets("X")
''''''''''''''''''''''''''''''''''''''''''''''
Sheets("1").Select
Sheets("1").Name = "Sheet1"
Sheets("2").Select
Sheets("2").Name = "Sheet2"

'insert the header line and the vlookup field
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''This is were it errors out''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("A:A").Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Procedure"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1] & "" "" & RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Selection.AutoFill Destination:=Range("A2:A14")
Range("A2:A14").Select
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Scheduled Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Selection.AutoFill Destination:=Range("A2:A15")
Range("A2:A15").Select
End Sub
 
G

Guest

Change Columns to Range

Range("A:A").Select
--
HTH...

Jim Thomlinson


philwants2bgolf said:
I get the error messege below when I run my macro. I can't figure out
what is wrong that causes this to happen. It breaks at
Columns("A:A").Select I have placed a comment field around it to help
make it stand out. Any help would be great.

By the way, my two file names are 1.txt and 2.txt


Error Messege:

Run-Time error'1004':

Application-defined or object -defined error





Code:
--------------------

Private Sub cmdImport_Click()
Dim schfile As Variant 'Scheduled input file with path
Dim procfile As Variant 'Case Procedure input file with path
Dim mainf As Variant 'Main program window name
Dim newf As Variant 'New file
Dim schwb As Variant 'Schedule Workbook file name
Dim prowb As Variant 'Case Procedure Workbook file name



mainf = ActiveWorkbook.Name

schfile = Application.GetOpenFilename(, , "Select Scheduled File")
If schfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

procfile = Application.GetOpenFilename(, , "Select Case Procedure File")
If procfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

Workbooks.Add
newf = ActiveWorkbook.Name

Workbooks.OpenText Filename:=schfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(20, 2), Array(31, 2), Array(72, 2))

schwb = ActiveWorkbook.Name
Workbooks.OpenText Filename:=procfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(19, 2), Array(26, 2), Array(68, 2), Array(79, 2))

procwb = ActiveWorkbook.Name


' copy and paste into one file
'
Windows(schwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("1").Select
Sheets("1").Move Before:=Workbooks(newf).Sheets(1)
Windows(procwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("2").Select
Sheets("2").Move Before:=Workbooks(newf).Sheets(2)
Sheets("Sheet1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: Sheets("X")
''''''''''''''''''''''''''''''''''''''''''''''
Sheets("1").Select
Sheets("1").Name = "Sheet1"
Sheets("2").Select
Sheets("2").Name = "Sheet2"

'insert the header line and the vlookup field
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''This is were it errors out''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("A:A").Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Procedure"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1] & "" "" & RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Selection.AutoFill Destination:=Range("A2:A14")
Range("A2:A14").Select
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Scheduled Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Selection.AutoFill Destination:=Range("A2:A15")
Range("A2:A15").Select
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

File Name changes with dates and Deleting Rows of Zeros 0
Complie Syntax Error 6
Macro should Save and Overwrite file 1
Different Results from the Same Macro 2
Can Vba workout if a selection is part of a group? 0
Create Detail Sheets from Pivot Table 1
macro to copy rows that contain a cell with a certain number 2
Help again!!! 1

Top