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
 

bensonsearch

Well-known Member
Joined
May 26, 2011
Messages
844
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

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

Well-known Member
Joined
May 26, 2011
Messages
844
the hard code i would use to select them is

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

Forum statistics

Threads
1,081,706
Messages
5,360,767
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top