Wildcard issue? 1

[RP1America]

This code...

Sub NewSheet()

Application.ScreenUpdating = False

Dim strNewSheetName As String

    Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
strNewSheetName = Cells(i, "D").Value & " " & Cells(i, "C").Value

Workbooks("Web Transfer Worksheet.xlt").Activate
Sheets("Template").Visible = True

    Sheets("Template").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = strNewSheetName

Dim strMonth As String
Dim strYear As String
Dim strDate As String

strDay = Format$(Day(Now), "00")
strMonth = Format$(Month(Now), "00")
strYear = Format$(Year(Now), "####")
strDate = strYear + strMonth + strDay

Workbooks(strDate + "*_OmniTransferErrorReport.csv").Activate

Next i

Sheets("Template").Visible = False

Application.ScreenUpdating = True

End Sub

...is throwing an error at this line...

Workbooks(strDate + "*_OmniTransferErrorReport.csv").Activate

What I can't figure out is why that doesn't work, when this does...

Sub OpenFile()

Dim strMonth As String
Dim strYear As String
Dim strDate As String

strDay = Format$(Day(Now), "00")
strMonth = Format$(Month(Now), "00")
strYear = Format$(Year(Now), "####")
strDate = strYear + strMonth + strDay

Workbooks.Open _
    ("I:\Ryan Plew\Web Transfer VBA\" + strDate + "*_OmniTransferErrorReport.csv")

End Sub

Thoughts?

 

[SkipVought]

hi,

have you looked at your string in the VB Editor. Put in a Debug.Print "I:\Ryan Plew\Web Transfer VBA\" + strDate + "*_OmniTransferErrorReport.csv"

and see what you get.
BTW, all you need

Workbooks.Open _
    ("I:\Ryan Plew\Web Transfer VBA\" & [highlight]Format(Date, "yyyymmdd")[/highlight] & "*_OmniTransferErrorReport.csv")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...is throwing an error at this line...


CODE
Workbooks(strDate + "*_OmniTransferErrorReport.csv").Activate

Is the workbook already open when this statement executes?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

BTW, all you need

CODE
Workbooks.Open _
("I:\Ryan Plew\Web Transfer VBA\" & Format(Date, "yyyymmdd") & "*_OmniTransferErrorReport.csv")

Thanks, Skip! Not quite sure what I was thinking with the strings.

I also updated the following...

Workbooks(Format(Date, "yyyymmdd") & "*_OmniTransferErrorReport.csv").Activate

...but am still not having any luck.

Yes, the workbook is already open.

 

[SkipVought]

And you absolutely know that it is open in the SAME INSTANCE of Excel as the workbook where your code is running?

Can you "see" THAT workbook in the VB Editor VBA Project Explorer where your code is running?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

It is open in the same instance of Excel. I can see that workbook in the Project window of the editor (both are there).

 

[RP1America]

BTW...Error is "Subscript out of range

 

[SkipVought]

That error indicates an incorrect workbook name.

Use the Watch Window to observe good stuff about your ovjects & varialbes in the VB Editor faq707-4594

It should help you debug the issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Thanks for all your help Skip! In digging deeper, I've come upon an adequate solution.

Dim curWorkbook As Workbook
Set curWorkbook = ActiveWorkbook

I put the above code at the beginning of my routine, then changed the line in question to:

curWorkbook.Activate

 

[SkipVought]

Dim curWorkbook As Workbook
Set curWorkbook = Workbooks.Open _
    ("I:\Ryan Plew\Web Transfer VBA\" & Format(Date, "yyyymmdd") & "*_OmniTransferErrorReport.csv")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}