UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Keep Excel File In Foreground To Allow User To Edit It, Office 2010    
 
   
TimTDP
post Feb 26 2012, 03:02 AM
Post #1

UtterAccess Guru
Posts: 677
I need to import an Excel file into Access.
However before I import it I need to ensure that the format of the Excel file adheres to certain rules.
I want to:
1. Open the file from within Access
2. Halt the code in Access until the Excel file has been closed. This will allow the user to ensure that the Excel file adheres to the rules.
3. When the Excel file has been closed, continue with the Access code.

I know how to do step one, but how do I steps 2 & 3?

Thanks in advance

This post has been edited by TimTDP: Feb 26 2012, 03:03 AM
Go to the top of the page
 
+
Doug Steele
post Feb 26 2012, 01:25 PM
Post #2

UtterAccess VIP
Posts: 17,620
From: Don Mills, ON (Canada)
Grab the code in Shell and Wait at "The Access Web" and add it to your database. Unfortunately, it requires that you know the location of Excel.exe, since you need to pass the path when you call the ShellWait routine:

CODE
  Call ShellWait("""C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"" ""D:\Documents\Collection\CDs I Own.xls""", vbNormalFocus)


However, here's the necessary code to be able to find the executable associated with any file:

CODE
Private Declare Function FindExecutable Lib "shell32" Alias "FindExecutableA" ( _
  ByVal lpFile As String, _
  ByVal lpDirectory As String, _
  ByVal sResult As String _
) As Long
    
Function LocateExecutable(PathToFile) as String
  
Dim lngPosition As Long
Dim lngResult As Long
Dim strFile As String
Dim strPath As String
Dim strPathToExecutable As String
  
  strFile = Dir(PathToFile)
  If Len(strFile) > 0 Then
    strPath = Left$(PathToFile, Len(PathToFile) - Len(strFile))
    strPathToExecutable = Space$(MAX_PATH)
      
    lngResult = FindExecutable(strFile, strPath, strPathToExecutable)
      
    If lngResult >= 32 Then
      lngPosition = InStr(strPathToExecutable, Chr$(0))
      If lngPosition > 0 Then
        LocateExecutable = Left$(strPathToExecutable, lngPosition - 1)
      End If
    End If
  End If
                            
End Function
Go to the top of the page
 
+
TimTDP
post Mar 7 2012, 05:57 AM
Post #3

UtterAccess Guru
Posts: 677
I get an error Max_Path

CODE
strPathToExecutable = Space$(MAX_PATH)


Variable not defined


This post has been edited by TimTDP: Mar 7 2012, 06:15 AM
Go to the top of the page
 
+
Doug Steele
post Mar 7 2012, 08:12 AM
Post #4

UtterAccess VIP
Posts: 17,620
From: Don Mills, ON (Canada)
Oops. Sorry about that.

Add the following line:

CODE
Private Const MAX_PATH As Long = 260


As in

CODE
Private Declare Function FindExecutable Lib "shell32" Alias "FindExecutableA" ( _
  ByVal lpFile As String, _
  ByVal lpDirectory As String, _
  ByVal sResult As String _
) As Long
    
Private Const MAX_PATH As Long = 260
  
Function LocateExecutable(PathToFile) as String
  
Dim lngPosition As Long
Dim lngResult As Long
Dim strFile As String
Dim strPath As String
Dim strPathToExecutable As String
  
  strFile = Dir(PathToFile)
  If Len(strFile) > 0 Then
    strPath = Left$(PathToFile, Len(PathToFile) - Len(strFile))
    strPathToExecutable = Space$(MAX_PATH)
      
    lngResult = FindExecutable(strFile, strPath, strPathToExecutable)
      
    If lngResult >= 32 Then
      lngPosition = InStr(strPathToExecutable, Chr$(0))
      If lngPosition > 0 Then
        LocateExecutable = Left$(strPathToExecutable, lngPosition - 1)
      End If
    End If
  End If
                            
End Function
Go to the top of the page
 
+
TimTDP
post Mar 7 2012, 08:27 AM
Post #5

UtterAccess Guru
Posts: 677
Thank you
Works like a dream
Go to the top of the page
 
+
« Next Oldest · Access Modules · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 20th May 2013 - 11:02 PM