CODEWITHSUNDEEP
vbaexcel
wes5550
wes5550

Reputation: 13

Code doesn't work in second macro

I'm pretty new to vba, and I could use some help. Here is the code am using. This is just a basic macro to call another macro and loop. However, it seems to be skipping everything from workbooks.open through activeworkbook.saveas. The annoying bit is that I have used this code twice before but with different file names and paths and everything worked.

I have stepped-through the code and it really just seems to be skipping those lines, which it does not do in the original code.

Sub oddball_macro_loop()
'
' oddball_macro_loop Macro
'
'

Dim rawPath As String
Dim rawFile As String
Dim savePath As String
Dim oWB As Workbook
Dim fName As Variant


rawPath = "I:\Cores\DMB\E-Prime Tasks\Salience Task\Data\Macros\Raw\For macro"
rawFile = Dir(rawPath & "*.txt")
savePath = "I:\Cores\DMB\E-Prime Tasks\Salience Task\Data\Macros\Processed"

ChDir (rawPath)
Application.DisplayAlerts = True

Do While rawFile <> ""
    Workbooks.Open Filename:="I:\Cores\DMB\E-Prime Tasks\Salience Task\Data\Macros\oddball_macro.xlsm"
    Set oWB = Workbooks.Open(rawPath & rawFile)
    Application.Run "'oddball_macro.xlsm'!oddball_macro"
    Sheets("Data").Select


'Sets File Name
    Dim text As String
    text = Range("Z12")
    fName = Mid(text, 19)
    ActiveWorkbook.SaveAs Filename:=savePath & fName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close False
    rawFile = Dir()
Loop

ActiveWorkbook.Close False
Application.ScreenUpdating = True


End Sub

Upvotes: 1

Views: 80

Answers (1)

David Zemens
David Zemens

Reputation: 53663

If it's skipping those lines then that suggests the result of the Dir() function is null.

it looks like you may be possibly missing a path separator at the end of rawPath? This should fix it:

rawFile = Dir(rawPath & Application.PathSeparator & "*.txt")

Or alternatively just make sure you add that to your assignment statment:

rawPath = "I:\Cores\DMB\E-Prime Tasks\Salience Task\Data\Macros\Raw\For macro\"

Often you'll see people do something like this as a sort of double-check:

If Not right(rawPath, 1) = Application.PathSeparator then 
    rawPath = rawPath & Application.PathSeparator
End If
rawFile = Dir(rawPath & "*.txt")

Or you could get real fancy and do this:

Dim sep as String
sep = Application.PathSeparator

rawFile = Dir(rawPath & IIF(Right(rawPath,1) = sep, "*.txt", sep & ".txt"))

Upvotes: 2

Related Questions