By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Open an excell spreedsheet from access 2007?

P: 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
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,599
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

P: 31
Thank you very much, I got it to work
Feb 4 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
When im done exporting to excel, I just do
Expand|Select|Wrap|Line Numbers
  1. myExcel.visible=true
Feb 4 '10 #4

Post your reply

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