Importing File Names

MelG

New Member
Joined
Jul 28, 2011
Messages
21
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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....
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top