Hi,
My Access VBA code successfully exports data in READ-ONLY Excel file (VehExpReport.xlsx) kept at a particular destination and when each time user need to save that excel file, he can save it at different locations as desired and the original read-only excel file shall be at the same path.
This was done successfully. My VBA code sets Excel Application, open the file from given path and adds number of sheets in VehExpReport.xlsx.
(data is actually Vehicle maintenance expenses) When my code start executing, it opens VehExpReport.xlsx file from the given destination and adds separate sheet for each vehicle with their maintenance expenses. Let us suppose if there are 5 vehicles then it will generate 5 sheets and 1 additional sheet at last called SUMMARY in which it is collecting all vehicle numbers and total expense that spent on the maintenance of that particular vehicle.
Up to here this is being carried out greatly.
When the Excel file VehExpReport.xlsx on the screen, I want a hyperlinks on each vehicle number mentioned onto the SUMMARY sheet so that user can click the vehicle number link and control will take directly to your referral sheet.
To do this I did a small work which is partly success. Mean the underscore with Blue text is generating over each Vehicle numbers on SUMMARY sheet but in fact nothing achieved.
Public Sub InsertDates()
Dim objXl As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rstNames As DAO.Recordset, rst As DAO.Recordset
Dim iRow As Integer, x As Long
Dim DRec As Recordset
Set rstNames = CurrentDb.OpenRecordset("SELECT VNo, SumofAmount FROM Q1;")
If rstNames.EOF And rstNames.BOF Then
MsgBox ("No Records In This Month"), vbInformation, "Null Records Inf."
Else
Set objXl = New Excel.Application
objXl.Visible = True
Set objWkb = objXl.Workbooks.Open("D:\VehicleDB\VehExpReport\VehExpReport.xlsx")
Do
Set objSht = objWkb.Worksheets.Add()
Set rst = CurrentDb.OpenRecordset("SELECT [T_VehExp.VNo], [T_VehExp.VEDate], [T_VehExp.JobNo], " & _
"[T_VehExp.InvoiceNo], [T_VehExp.VehicleModel], [T_VehExp.VDate], [T_VehExp.Expense Description], [T_VehExp.Amount], " & _
"[T_VehExp.KM], [T_VehExp.Remarks], [T_VehExp.Part], [T_VehExp.MechanicName] FROM T_VehExp " & _
"WHERE [T_VehExp.VNo] ='" & rstNames![VNo] & "' ;")
If rst.EOF And rst.BOF Then
.....
...Code lines ....
Below is the problmatic...
'===================================================================
' ADD MAIN-ADDRESS
objXl.Cells.Hyperlinks.Add _
Anchor:=objXl.Cells(SRow, 2), _
Address:="", _
SubAddress:="", _
ScreenTip:="-", _
TextToDisplay:=""
' ' ADD SUB-ADDRESS
' objSht("Sheet2").Hyperlinks.Add _
' Anchor:=objSht(rstNames![VNo]).Cells(cell.Row, 1), _
' Address:="", _
' SubAddress:=objSht.Name & "rstNames![VNo]" & cell.Address, _
' ScreenTip:="", _
' TextToDisplay:=""
'===================================================================
Instead of copying code at this forum, I have would like to attach my db with few records for your ref. unfortunately, it looks the net is supporting to his Please check and help me out.
Thanks in advance
Ashfaque
Posted by: ashfaque_online@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar