Sleeper: Help refreshing data

[higgins1b]

I have a VB application that opens a spreadsheet which has a refresh all macro that is supposed to run when the spreadsheet is opened, and it works fine when I open it by clicking the spreadsheet. My problem is when I try to open it through my VB application the macro runs, and I get the message "connecting to database", but when it finishes I don't see any data. My VB application uses the code "system.diagnostics.process.start("excel.exe","spreadsheet name")

Can anyone explain why the data would not come into the spreadsheet when I try to open it using my VB application?

[mark007]

So the macro definately runs but no data appears?

Can we see your macro code?

[higgins1b]

Yes it definitely runs. It says it is connecting to the database and it does the copy and paste, but there is nothing to copy.



Private Sub Workbook_Open()
Application.Run "ThisWorkbook.prosecution"
End Sub

Sub prosecution()
'prosecution Macro
If MyName = "prosecution.xls" Then
Application.DisplayAlerts = False
Dim filename As String
filename = "prosecution_" & Month(Date) & (Day(Date)) & ".xls"
ActiveWorkbook.RefreshAll
Worksheets("Client_data").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
With Worksheets("Client_data")
.Range("ClientData2").Copy
Worksheets("Shift_prosecution_full").Range("B2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Shift_prosecution_AM").Range("B2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Shift_prosecution_PM").Range("B2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

[mark007]

stuff

I've edited your post above to add them :)

[mark007]

A few thoughts spring to mind.

Firstly as the procedure is in the same workbook as the ThisWorkbook module you can call it dircetly rather than using .Run.



Private Sub Workbook_Open()
prosecution
End Sub

I also note you are checking a value MyName but it doens't seem to be set anywhere.

[higgins1b]

Thanks Mark, I will look into that. The value MyName is in another module. I really appreciate the help, it is really important that this works.

[mark007]

No problem - let me know if you sort it or need further help.

:)