R
rgrantz
I have a button that exports a query to an Excel Workbook, and I use the
query's Start and End Date parameters (set in a form) to make the Worksheet
name (please not there is lots of wrapping):
Dim SheetName As String
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryDesignTimeMgmt4XL", "C:\Folder\DesignTimeMgmt.xls", True,
Format(Me.StartDate, "mm") & "-" & Format(Me.StartDate, "dd") & "-" &
Format(Me.StartDate, "yy") & "THRU" & Format(Me.EndDate, "mm") & "-" &
Format(Me.EndDate, "dd") & "-" & Format(Me.EndDate, "yy")
This places a Worksheet with the form's StartDate and EndDate as the name
(ie. 1-3-05THRU2-1-05).
I then want to give the user the option to open the workbook at the new
sheet:
If MsgBox("Data Exported and saved in Excel: Do you wish to open the excel
file?", vbYesNo, "Export Successful") = vbYes Then
SheetName = Format(Me.StartDate, "mm") & "-" & Format(Me.StartDate, "dd") &
"-" & Format(Me.StartDate, "yy") & "THRU" & Format(Me.EndDate, "mm") & "-" &
Format(Me.EndDate, "dd") & "-" & Format(Me.EndDate, "yy")
Application.FollowHyperlink "c:\Folder\DesignTimeMgmt.xls", "'" & SheetName
& "'!A1"
End If
Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Number & Err.Description
Resume Exit_Command9_Click
However, the subaddress of the Followhyperlink is not working. I can get it
to work when I specifically name a sheet rather than using the variable
"SheetName", but something about the syntax of the subaddress and the use of
the variable gives me a "reference not valid" error when the excel workbook
loads. I'm hoping it's a simple matter of the placement and use of the
single and double-quotes, but I've tried what I believe is every single
possible permutation. Is it possible to use a variable in the subaddress
(worksheetname) of a Followhyperlink? If so, what is the exact syntax
needed?
Thanks for any help, I tried this for about 4 hours, and could only find one
post about specific worksheet names using followhyperlink in all of the
newsgroups, and that was using specific sheet names rather than a variable.
PS: I can't use Outputto, as this does not let me name the worksheet.
Thanks for reading
query's Start and End Date parameters (set in a form) to make the Worksheet
name (please not there is lots of wrapping):
Dim SheetName As String
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryDesignTimeMgmt4XL", "C:\Folder\DesignTimeMgmt.xls", True,
Format(Me.StartDate, "mm") & "-" & Format(Me.StartDate, "dd") & "-" &
Format(Me.StartDate, "yy") & "THRU" & Format(Me.EndDate, "mm") & "-" &
Format(Me.EndDate, "dd") & "-" & Format(Me.EndDate, "yy")
This places a Worksheet with the form's StartDate and EndDate as the name
(ie. 1-3-05THRU2-1-05).
I then want to give the user the option to open the workbook at the new
sheet:
If MsgBox("Data Exported and saved in Excel: Do you wish to open the excel
file?", vbYesNo, "Export Successful") = vbYes Then
SheetName = Format(Me.StartDate, "mm") & "-" & Format(Me.StartDate, "dd") &
"-" & Format(Me.StartDate, "yy") & "THRU" & Format(Me.EndDate, "mm") & "-" &
Format(Me.EndDate, "dd") & "-" & Format(Me.EndDate, "yy")
Application.FollowHyperlink "c:\Folder\DesignTimeMgmt.xls", "'" & SheetName
& "'!A1"
End If
Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Number & Err.Description
Resume Exit_Command9_Click
However, the subaddress of the Followhyperlink is not working. I can get it
to work when I specifically name a sheet rather than using the variable
"SheetName", but something about the syntax of the subaddress and the use of
the variable gives me a "reference not valid" error when the excel workbook
loads. I'm hoping it's a simple matter of the placement and use of the
single and double-quotes, but I've tried what I believe is every single
possible permutation. Is it possible to use a variable in the subaddress
(worksheetname) of a Followhyperlink? If so, what is the exact syntax
needed?
Thanks for any help, I tried this for about 4 hours, and could only find one
post about specific worksheet names using followhyperlink in all of the
newsgroups, and that was using specific sheet names rather than a variable.
PS: I can't use Outputto, as this does not let me name the worksheet.
Thanks for reading