473,322 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Open an excell spreedsheet from access 2007?

31
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdgraph_Click()
  2. Dim designtype As String
  3.  
  4. On Error GoTo err_Handler
  5.  
  6.    designtype = InputBox("Please Enter The Design Type", "Design Type")
  7.    MsgBox ExportRequest(designtype), vbInformation, "Finished"
  8.  
  9.  
  10.    Application.FollowHyperlink ("N:\Pd0013\Operations\Bar data\Bar Elactrical Data.xlsx")
  11.  
  12.    lblmsg.Caption = "Status"
  13.  
  14. exit_Here:
  15.    Exit Sub
  16. err_Handler:
  17.    MsgBox Err.Description, vbCritical, "Error"
  18.    Resume exit_Here
  19.  
  20. End Sub
  21.  
  22. Public Function ExportRequest(designtype) As String
  23.    On Error GoTo err_Handler
  24.  
  25.    ' Excel object variables
  26.    Dim appExcel As Object
  27.    Dim ExcelWorkbook As Object
  28.    Dim wbk As Excel.Workbook
  29.    Dim wks As Excel.Worksheet
  30.  
  31.    Dim sTemplate As String
  32.    Dim sTempFile As String
  33.    Dim sOutput As String
  34.    Dim parameter As String
  35.  
  36.    Dim dbs As DAO.Database
  37.    Dim rst As DAO.Recordset
  38.    Dim qdf As DAO.QueryDef
  39.    Dim prm As DAO.parameter
  40.    Dim sSQL As String
  41.    Dim lRecords As Long
  42.    Dim iRow As Integer
  43.    Dim iCol As Integer
  44.    Dim iFld As Integer
  45.  
  46.    Const cTabTwo As Byte = 1
  47.    Const cStartRow As Byte = 8
  48.    Const cStartColumn As Byte = 4
  49.  
  50.    DoCmd.Hourglass True
  51.  
  52.    'forcing the parameter of the query to the value inputed into the form
  53.    parameter = designtype
  54.  
  55.    ' set to break on all errors
  56.    Application.SetOption "Error Trapping", 0
  57.  
  58.    ' start with a clean file built from the template file
  59.    sTemplate = "N:\Pd0013\Operations\Bar data\bedt.xlsx"
  60.    sOutput = "N:\Pd0013\Operations\Bar data\Bar Electrical Data.xlsx"
  61.  
  62.    If Dir(sOutput) <> "" Then Kill sOutput
  63.    FileCopy sTemplate, sOutput
  64.  
  65.    ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
  66.    'Set appExcel = Excel.Application
  67.  
  68.    Set appExcel = CreateObject("Excel.Application")
  69.    Set wbk = appExcel.Workbooks.Open(sOutput)
  70.    Set wks = appExcel.Worksheets(cTabTwo)
  71.  
  72.  
  73.  
  74.    ' looking for the parameters of the query
  75.    Set dbs = CurrentDb
  76.    Set qdf = dbs.QueryDefs!selectbuildquery
  77.    For Each prm In qdf.Parameters
  78.         prm.Value = parameter
  79.     Next prm
  80.  
  81.  
  82.    Set rst = qdf.OpenRecordset(dbOpenDynaset)
  83.    If Not rst.BOF Then rst.MoveFirst
  84.  
  85.    ' For this template, the data must be placed on the 8th row, 2nd column.
  86.    ' (these values are set to constants for easy future modifications)
  87.    iCol = cStartColumn
  88.    iRow = cStartRow
  89.  
  90.  
  91.    Do Until rst.EOF
  92.       iFld = 0
  93.       lRecords = lRecords + 1
  94.       Me.lblmsg.Caption = "Exporting record #" & lRecords & " to Bar Electrical Data.xlsx"
  95.       Me.Repaint
  96.  
  97.       For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
  98.          wks.Cells(iRow, iCol) = rst.Fields(iFld)
  99.  
  100.          If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
  101.             wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
  102.          End If
  103.  
  104.          wks.Cells(iRow, iCol).WrapText = False
  105.          iFld = iFld + 1
  106.       Next
  107.  
  108.       wks.Rows(iRow).EntireRow.AutoFit
  109.       iRow = iRow + 1
  110.       rst.MoveNext
  111.    Loop
  112.  
  113.  
  114.    ExportRequest = "Total of " & lRecords & " rows processed."
  115.  
  116. exit_Here:
  117.    ' Cleanup all objects  (resume next on errors)
  118.    On Error Resume Next
  119.    Set wks = Nothing
  120.    Set wbk = Nothing
  121.    Set appExcel = Nothing
  122.    Set rst = Nothing
  123.    Set dbs = Nothing
  124.    DoCmd.Hourglass False
  125.    Exit Function
  126.  
  127. err_Handler:
  128.    ExportRequest = Err.Description
  129.    Me.lblmsg.Caption = Err.Description
  130.    Resume exit_Here
  131.  
  132. End Function
Feb 4 '10 #1
3 2371
ADezii
8,834 Expert 8TB
See if the following works, 'after' changing the Constant representing the Path to the Excel Executable:
Expand|Select|Wrap|Line Numbers
  1. Dim varRet
  2. Const conPATH_TO_EXCEL As String = "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE"
  3. Const conPATH_TO_XLXS As String = "N:\Pd0013\Operations\Bar data\Bar Elactrical Data.xlsx"
  4.  
  5. varRet = Shell(conPATH_TO_EXCEL & " " & Chr$(34) & conPATH_TO_XLXS & Chr$(34), vbMaximizedFocus)
Feb 4 '10 #2
tomric
31
Thank you very much, I got it to work
Feb 4 '10 #3
TheSmileyCoder
2,322 Expert Mod 2GB
When im done exporting to excel, I just do
Expand|Select|Wrap|Line Numbers
  1. myExcel.visible=true
Feb 4 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
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...
4
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 :...
1
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...
2
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)...
2
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.
1
by: radom | last post by:
how do I make vb2008 read/write to excell 2007
2
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 : ...
1
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...
12
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...
0
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...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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)...
1
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....
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.