I have a form that launches queries and one of them dumps 3 separate queries' data to separate tabs of one excel workbook. Previously I was having VBA create the workbook at the root C:\ drive, but recently our IT folks have prevented any users from writing directly to the C:\ drive. This problem is further compounded by the fact that I have users using 2 different computer networks, on one the user profiles are in the C:\ drive, but on the other they are in the D:\ drive (which the first network does not have). Below is the working VBA code I was using to export, format, and open the data when I was able to have users write to the C:\ drive:

If Me.QUERYLIST.Value = "Manning Detail" Then
Me.lblBuildingReport.Visible = True
Me.Repaint
DoCmd.Close acQuery, "X MANNING DETAIL"
DoCmd.Close acQuery, "Y MANNING DETAIL"
DoCmd.Close acQuery, "Z MANNING DETAIL"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "X MANNING DETAIL", "C:\Manning Detail.xlsx", , "XMANNING"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Y MANNING DETAIL", "C:\Manning Detail.xlsx", , "YMANNING"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Z MANNING DETAIL", "C:\Manning Detail.xlsx", , "ZMANNING"

FormatExcelBasic "C:\Manning Detail.xlsx", "XMANNING"
FormatExcelBasic "C:\Manning Detail.xlsx", "YMANNING"
FormatExcelBasic "C:\Manning Detail.xlsx", "ZMANNING"

Application.FollowHyperlink "C:\Manning Detail.xlsx"
Me.lblBuildingReport.Visible = False
Me.Repaint
End If

I have been able to get the queries to write and format by changing the instances of "C:\Manning Detail.xlsx" to just "Manning Detail.xlsx" but the command to open the workbook (Application.FollowHyperlink) does not work without a path to the file. Is there a way to point to the workbook in the "My Documents" folder that works regardless of the Drive on which it resides? Is there something another command I should be using?

Thanks in advance.

Here is code that determines the path of the my docs folder on your computer and appends Manning Detail.xlsx to the end:



EDIT: After looking at this closer this actually won't work since Environ$("USERPROFILE") returns the root directory and if I am understanding you correctly some users have the my docs folder somewhere other than the root. Is that accurate.

Ok I figured it out. This should work.

That is correct. I used this function to find the My Documents folder, and it seems to be working on the network with the foler on the root:

Function MyDocuments() As String
Dim objFSO As Object
Dim objShell As Object
Dim objFolder As Object
Dim objFolderItem As Object

Const MY_DOCUMENTS = &H5&

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Shell.Application")

Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self
MyDocuments = objFolderItem.Path

End Function

It seems to open fine using this command:

Application.FollowHyperlink MyDocuments & "\Manning Detail.xlsx"

However, when it does open, I get an annoying dialogue box that states: Some files can contain viruses or otherwise be harmful to your computer. It is important to be certain that this file is from a trustworthy souurce. Would you like to open this file?

Is there a way to supress this dialogue box? I am worried that users will not notice it since it pops before Excel actually opens and then Excel steals the focus and opens on top of it.

By supress do you mean that you don't want the user to see the dialogue box at all or you want it to pop up after the excel file opens?

Not at all. I just don't see the point in warning the user about a file they just created.

Try this:

That is giving me a compile error: Method or data member not found.

I think the issue is that it is not an Excel Alert, but a Microsoft Office alert. At least, "Microsoft Office" is in the title bar of the dialogue box. Also, the dialogue box has an Access icon in the taskbar.

How about this:

Tried that too. No luck, still pops-up.

Hi

Have you tried AJB's GoHyperlink

http://allenbrowne.com/func-GoHyperlink.html

Ian

Still getting the Alert even with the GoHyperlink module. This is driving me nuts. Could it have something to do with the security settings that the IT folks have on my machine? As I said before, this wasn't an issue until they prevented us from writing to the root folder.

I figured it out. This is what I ended up doing for anyone who cares:

Set oXL = CreateObject("Excel.Application")
With oXL
.Visible = True
.Workbooks.Open (MyDocuments & "\Manning Detail.xlsx")
End With

Thanks everyone for the help.