Importing File Names

[MelG]

Excel 2003

Hi,

I've mixed and matched some code to import file names into Excel. I can get the importing bit to work but there's one line of code that I get a runtime 1004 error. I've underlined the piece of code it gets stuck on which appears fine as I got it by recording a Macro.

Private Sub Submit_Click()

Dim dlgOpen As FileDialog
Dim vrtSelectedItem As Variant
Dim wsPending As Worksheet
Dim wsProcessed As Worksheet
Dim strFile As String
Dim i As Integer
Sheets.Add
ActiveSheet.Name = "Pending"
Set wsPending = Application.ActiveSheet
Sheets.Add
ActiveSheet.Name = "Processed"
Set wsProcessed = Application.ActiveSheet
Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen)
MsgBox "Select any file from the ABG8 Pending folder"
With dlgOpen
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
strFile = Dir("")
wsPending.Cells(4, 1) = strFile
i = 2
Do
strFile = Dir
wsPending.Cells(i + 3, 1) = strFile
i = i + 1
Loop Until "" = strFile
Next vrtSelectedItem
End If
End With
Set dlgOpen = Nothing
Sheets("Pending").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],8)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],10,4)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-3],15,3)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""SZ"",RC[-4])),""Final"",""Interim"")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-5],19,25)"

Selection.End(xlToLeft).Offset(0, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Offset(3, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Cells.Replace What:=".docx", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Cells.Replace What:=".doc", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Columns("F:F").Select

Selection.Replace What:="SZ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

 

[bensonsearch]

Excel 2003

Hi,

I've mixed and matched some code to import file names into Excel. I can get the importing bit to work but there's one line of code that I get a runtime 1004 error. I've underlined the piece of code it gets stuck on which appears fine as I got it by recording a Macro.

Private Sub Submit_Click()

Dim dlgOpen As FileDialog
Dim vrtSelectedItem As Variant
Dim wsPending As Worksheet
Dim wsProcessed As Worksheet
Dim strFile As String
Dim i As Integer
Sheets.Add
ActiveSheet.Name = "Pending"
Set wsPending = Application.ActiveSheet
Sheets.Add
ActiveSheet.Name = "Processed"
Set wsProcessed = Application.ActiveSheet
Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen)
MsgBox "Select any file from the ABG8 Pending folder"
With dlgOpen
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
strFile = Dir("")
wsPending.Cells(4, 1) = strFile
i = 2
Do
strFile = Dir
wsPending.Cells(i + 3, 1) = strFile
i = i + 1
Loop Until "" = strFile
Next vrtSelectedItem
End If
End With
Set dlgOpen = Nothing
Sheets("Pending").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],8)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],10,4)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-3],15,3)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""SZ"",RC[-4])),""Final"",""Interim"")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-5],19,25)"

Selection.End(xlToLeft).Offset(0, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Offset(3, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Cells.Replace What:=".docx", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Cells.Replace What:=".doc", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Columns("F:F").Select

Selection.Replace What:="SZ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

the only thing i can see is that "selection" is not set to a value. so it would try to select someting that doesnt exsist....

 

[MelG]

I'm not sure what you mean by selection is not set to a value. Before the Range(Selection, Selection.End(xlToRight)).Select the active cell is B4 and I want it to select from B4 to the last cell on the right which is F4. Cells B4 to F4 all have data in them or should I say data derived from a formula.

I've tried to replace the above code with both codes below but still can't get it to work.

Range("B4:F4").Select and also Cells("B4:F4").Select

 

[bensonsearch]

the hard code i would use to select them is

thisworkbook.sheets("Sheet1").range("B4:F4").select

 

[MelG]

Thanks got it working now

 

[bensonsearch]

Thanks got it working now

Good to hear