PDA

View Full Version : Solved: Controlling Dialog Boxes



gemsera
08-06-2008, 02:56 AM
Hi All,

Im sure this has been asked before but a search yielded no specific results.

I am trying to get excel to upload a file to a sharepoint site(just a network directory), using the save as with the DIR and variable (date) filename. This part works fine.

The problem is when Excel brings up three dialog boxes,

1) Version Number (always 1.0), Status (default is ok) then (OK, Cancel)

2) Check in file - Radio Buttons (default is OK)

3) Comments (need to enter "Daily SPIR Report &date") then OK

Does anyone have any knowledge in relation to this? I have tried using sendkeys (ugly, I know) however they run too fast, and the wait function pauses excel AND vba and doesnt allow the dialog boxes to come up.

Many thanks for help in advance.:friends:

Kenneth Hobs
08-06-2008, 05:19 AM
I gather you are using SendKeys? If so, try Application.Sendkeys.

gemsera
08-06-2008, 05:32 AM
*edit*
Its not working, neither application.SendKeys nor Call SendKeys. Its too quick/timed wrong. I will have a play around with it and report back.

Still not working. Sorry to be a pain :(

*additional edit* Sorry about my manners, thank you very much for your help :)

Kenneth Hobs
08-06-2008, 06:05 AM
That is why most avoid SendKeys but sometimes it is the only way to get a solution.

I am not sure that it is Excel that is showing the dialogs. If you posted the code, I might be able to tell. I can only test in v2003 so it may be a v2007 thing.

Try saving to a local folder first. I would do this anyway. You can then do a copy to the network location. This will leave the workbook open in the local folder. If you are setting Property with (3), set it before the Save command.

gemsera
08-06-2008, 06:19 AM
The code is relatively simple, however I think you are right - I think excel shared workbooks is creating the dialog boxes.


Sub uploadtest()


Dim dir As String
Dim myFileName As String
dir = "http://home/Departments/PS/EMEAPS/EMEA Projects/Projects/Thisproject/Shared Documents/11a)Testing/SPIR Reports/"
myFileName = dir & "SPIR Report" & "(" & Format(Now(), "DD-MMM-YYYY") & ").xls"



ActiveWorkbook.SaveAs Filename:=myFileName



ActiveWorkbook.Close (False)

Application.SendKeys ("1.0")
Application.SendKeys ("{enter}")
Application.SendKeys ("{tab}")
Application.SendKeys ("{enter}")
Application.SendKeys ("Daily SPIR Report")
Application.SendKeys ("{tab}")
Application.SendKeys ("{enter}")



End Sub



there is the code (cleaned for security of course). I have also attacheda screenshot of the dialog boxes that come up, if that helps at all...

gemsera
08-07-2008, 06:17 AM
does anyone have any pointers? :)

gemsera
08-11-2008, 02:51 AM
For future reference, checking a document into sharepoint can be done this way:


ActiveWorkbook.CheckIn SaveChanges:=True, _
Comments:="Daily SPIR Report"

Pretty easy. Still no solution for the Version dialog box.

gemsera
08-11-2008, 02:55 AM
For some reason, this code bypasses the version requirement.

Sub uploadtest()

Dim dir As String
Dim myFileName As String
dir = "http://home/Departments/PS/EMEAPS/EMEA Projects/Projects/Thisproject/Shared Documents/Testing/Reports/"
myFileName = dir & "Report" & "(" & Format(Now(), "DD-MMM-YYYY") & ").xls"

ActiveWorkbook.SaveAs Filename:=myFileName


ActiveWorkbook.CheckIn SaveChanges:=True, _
Comments:="Daily SPIR Report"
ActiveWorkbook.Close

End Sub

Seems to work. Thanks for everyones help.