I'm having a problem open an excel spreedsheet that I transfered data to from access. It use to work in access 2003, the we changed over to office 2007 and the code quit working. On the line "Application.FollowHyperlink" I get an error "Cannot open specified file" In the subroutine I call it does open the file and transfers data to it, but I can't get it to open once the data is there. Any advice on how to fix this. - Private Sub cmdgraph_Click()
-
Dim designtype As String
-
-
On Error GoTo err_Handler
-
-
designtype = InputBox("Please Enter The Design Type", "Design Type")
-
MsgBox ExportRequest(designtype), vbInformation, "Finished"
-
-
-
Application.FollowHyperlink ("N:\Pd0013\Operations\Bar data\Bar Elactrical Data.xlsx")
-
-
lblmsg.Caption = "Status"
-
-
exit_Here:
-
Exit Sub
-
err_Handler:
-
MsgBox Err.Description, vbCritical, "Error"
-
Resume exit_Here
-
-
End Sub
-
-
Public Function ExportRequest(designtype) As String
-
On Error GoTo err_Handler
-
-
' Excel object variables
-
Dim appExcel As Object
-
Dim ExcelWorkbook As Object
-
Dim wbk As Excel.Workbook
-
Dim wks As Excel.Worksheet
-
-
Dim sTemplate As String
-
Dim sTempFile As String
-
Dim sOutput As String
-
Dim parameter As String
-
-
Dim dbs As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim qdf As DAO.QueryDef
-
Dim prm As DAO.parameter
-
Dim sSQL As String
-
Dim lRecords As Long
-
Dim iRow As Integer
-
Dim iCol As Integer
-
Dim iFld As Integer
-
-
Const cTabTwo As Byte = 1
-
Const cStartRow As Byte = 8
-
Const cStartColumn As Byte = 4
-
-
DoCmd.Hourglass True
-
-
'forcing the parameter of the query to the value inputed into the form
-
parameter = designtype
-
-
' set to break on all errors
-
Application.SetOption "Error Trapping", 0
-
-
' start with a clean file built from the template file
-
sTemplate = "N:\Pd0013\Operations\Bar data\bedt.xlsx"
-
sOutput = "N:\Pd0013\Operations\Bar data\Bar Electrical Data.xlsx"
-
-
If Dir(sOutput) <> "" Then Kill sOutput
-
FileCopy sTemplate, sOutput
-
-
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
-
'Set appExcel = Excel.Application
-
-
Set appExcel = CreateObject("Excel.Application")
-
Set wbk = appExcel.Workbooks.Open(sOutput)
-
Set wks = appExcel.Worksheets(cTabTwo)
-
-
-
-
' looking for the parameters of the query
-
Set dbs = CurrentDb
-
Set qdf = dbs.QueryDefs!selectbuildquery
-
For Each prm In qdf.Parameters
-
prm.Value = parameter
-
Next prm
-
-
-
Set rst = qdf.OpenRecordset(dbOpenDynaset)
-
If Not rst.BOF Then rst.MoveFirst
-
-
' For this template, the data must be placed on the 8th row, 2nd column.
-
' (these values are set to constants for easy future modifications)
-
iCol = cStartColumn
-
iRow = cStartRow
-
-
-
Do Until rst.EOF
-
iFld = 0
-
lRecords = lRecords + 1
-
Me.lblmsg.Caption = "Exporting record #" & lRecords & " to Bar Electrical Data.xlsx"
-
Me.Repaint
-
-
For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
-
wks.Cells(iRow, iCol) = rst.Fields(iFld)
-
-
If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
-
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
-
End If
-
-
wks.Cells(iRow, iCol).WrapText = False
-
iFld = iFld + 1
-
Next
-
-
wks.Rows(iRow).EntireRow.AutoFit
-
iRow = iRow + 1
-
rst.MoveNext
-
Loop
-
-
-
ExportRequest = "Total of " & lRecords & " rows processed."
-
-
exit_Here:
-
' Cleanup all objects (resume next on errors)
-
On Error Resume Next
-
Set wks = Nothing
-
Set wbk = Nothing
-
Set appExcel = Nothing
-
Set rst = Nothing
-
Set dbs = Nothing
-
DoCmd.Hourglass False
-
Exit Function
-
-
err_Handler:
-
ExportRequest = Err.Description
-
Me.lblmsg.Caption = Err.Description
-
Resume exit_Here
-
-
End Function
3 2371
See if the following works, 'after' changing the Constant representing the Path to the Excel Executable: - Dim varRet
-
Const conPATH_TO_EXCEL As String = "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE"
-
Const conPATH_TO_XLXS As String = "N:\Pd0013\Operations\Bar data\Bar Elactrical Data.xlsx"
-
-
varRet = Shell(conPATH_TO_EXCEL & " " & Chr$(34) & conPATH_TO_XLXS & Chr$(34), vbMaximizedFocus)
Thank you very much, I got it to work
When im done exporting to excel, I just do Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Halstead |
last post by:
Hi guys, I have a asp.net web app and it's connecting to
an excell 2003 document (in another folder) via
OleDataAdapter. Everything works and all, it's just that
when I shutdown IE and try to...
|
by: Leeor Chernov |
last post by:
Hi ,
I Used the object Excell Application(COM INTEROP) and it is staying as an
open process, And the owner(User Name) is ASPNET
Altough I try to release it with :...
|
by: Kriston Scott |
last post by:
I am trying to sort the rows in an Excel spreedsheet using Perl and the Win32::IDE. I tried converting the code from a macro that sorts rows in Excel into Perl code, but I have not been able to get...
|
by: Twanne |
last post by:
Hi,
I've got these files in excell that need to be imported to access. Now I've got one file, and when I try to import it, it acts weird. There are two fields in the excell fiile (Start, Stop)...
|
by: Aniasp |
last post by:
hi
Please let me know the procedure to post data from MS-Access to MS-Excell using ASP ! as well as Excell to Access ! Plz !
Thanks.
|
by: radom |
last post by:
how do I make vb2008 read/write to excell 2007
|
by: Bram2008 |
last post by:
How to use macro in excel?
Can i get some code to record excell data to vb?
O y, i have project in vb, and i write data in vb, then i transfer it to excell to build chart. This is my code :
...
|
by: ARC |
last post by:
I think I made a major blunder, and now Access 2007 no longer opens. I made
the mistake yesterday of using the "test" option in wise installer for my
Access 2007 runtime app. It actually installed...
|
by: godiva |
last post by:
Hi,
Last week one client had errors caused by another program which led
the IT guys to wipe out and rebuild the hard drive on one particular
computer. Prior to this happening, the people in this...
|
by: jzalar |
last post by:
I have Access 2007, Excel 2007, Access 97 and Excel 97 running on my Windows XP computer. When I try to open up a Pivot Form in Access 97, Excel 2007 Opens. I can not figure how to make Excel 97...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |