open file while current date is changing on file name

[Hlatigo]

Good morning everyone!

I am working on some code that extracts a file with the name EODVAL_11.16.05.xls

The thing is, the file's date changes everyday and i would like to have it open if not already opened and that way i can extract the current day information.


I have the code below but i dont think i have the file path placed in the right area. I took this code off of here and changed it up a little but I kinda ran into a wall right now. If anyone wants to help...i would be most appreciative.


Private Sub Workbook_Open()

Dim x As Workbook
Dim c As Long


Const TestFilename As String = "EODVAL_ & c.xls"

c = DateAdd("d", -1, today())

On Error Resume Next
Set x = Workbooks(TestFilename)
On Error GoTo 0
If x Is Nothing Then
On Error Resume Next
Set x = Workbooks.Open(ThisWorkbook.R:\MidOffice\Data\Valuation\Daily Validation\EOD VAL, 0)
On Error GoTo 0
If x Is Nothing Then
MsgBox TestFilename & " not open and not found"
Exit Sub
End If
End If
ThisWorkbook.Windows(1).Activate
End Sub

End Sub

 

[Von Pookie]

How about this?

Private Sub Workbook_Open()
Dim ans As Variant
Dim FileName As String, FullName As String

Const FilePath = "R:\MidOffice\Data\Valuation\Daily Validation\EOD VAL\"

FileName = "EODVAL_" & Format(Date, "mm.dd.yy") & ".xls"
FullName = FilePath & FileName

MsgBox FullName
If WorkbookIsOpen(FullName) = True Then
    Exit Sub
Else
    ans = MsgBox(FileName & " is not open." & vbCrLf & vbCrLf _
    & "Would you like to open the file now?", vbYesNo)
    
    If ans = vbYes Then
        Workbooks.Open (FullName)
    Else
        Exit Sub
    End If
End If

End Sub

Private Function WorkbookIsOpen(wbname) As Boolean
'   Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True Else WorkbookIsOpen = False

End Function

 

[Hlatigo]

Hi Kristy!

Thanks so very much, the code goes all the way up to it asking me if I want to open the file. Once I click yes i get a run time error 1004 "application defined or object-defined error.

The workbook is closed so I dont think there should be a problem and it the workbook does exist.

Any thoughts?

WHat if it is already opened bu someone else? thats not the case here but if it is...will it open anyway? is it possible to have it open in read-only format? I assume read-only because thats the option i get when I try opening when someone else is in?

Thanks!

 

[Von Pookie]

I guess the error *could* be due to someone else having the workbook open, but I'm not sure. I've never used shared workbooks before.

I can poke around a bit, but no promises I'll find anything

 

[Hlatigo]

Hiya Kristy!!!

I found out the problem. I have a space between EOD and VAL. I sent it to you without the space...your code is perfect and of course mine is sad. hahaha.

One quick question. WHen I open the workbook it asks if i want to update, which I dont. Should i just write something that turns it off? SInce it is another workbook would this work?

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

 

[Von Pookie]

I did a quick search of the board; maybe this will work.

Try changing this:

If ans = vbYes Then
        Workbooks.Open (FullName)

to this

If ans = vbYes Then
        Application.AskToUpdateLinks = False 
        Workbooks.Open (FullName)
        Application.AskToUpdateLinks = True

I haven't tested this, though.