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

Open a file on a network drive

P: n/a
Jim
This should be simple...

I have Access export a query to an Excel file on the network at:

Y:\Enterprise\CLD_OE\POP Database\FILE.xls

After the visual basic code is done exporting the file to FILE.xls, I
want VB to open the file so it can be viewed and edited onscreen.

If at all possible I'd additionally want to Access to run some
formatting of the Excel file through visual basic.

Aug 9 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The 'Shell' statement is probably the only one you care about:

<compiled code>
Private Sub btnSendToExcel_Click()
Dim wkPath As String
Dim wkFile As String
Dim wkQuery As String

On Error GoTo btnVWorkbookErr

wkPath = DLookup("[usTempPath]", "uSysCtl", "[usID] = 1")
wkQuery = Me.txtQueryName
wkFile = wkPath & "\" & Replace(Replace(Mid$(Me.txtQueryName, 8), "
", "_"), ":", "_") & IIf(Me.Parent.chkInsertTimeStamp = True, "_" &
DateStr(), "") & ".xls"
Kill wkFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "@" &
Mid$(wkQuery, 8), wkFile, True
Shell "EXCEL.EXE """ & wkFile & "", vbNormalFocus
DoEvents

btnVWorkbookExit:
Exit Sub

btnVWorkbookErr:
Select Case Err
Case 53: Resume Next
Case Else
Forms("frmMain").ErrLog "ViewWorkbook", Err, Err.Description,
wkFile
Resume Next
End Select
End Sub

</compiled code>

Ron, King of Chi

On Aug 9, 3:23 pm, Jim <jlrehm...@gmail.comwrote:
This should be simple...

I have Access export a query to an Excel file on the network at:

Y:\Enterprise\CLD_OE\POP Database\FILE.xls

After the visual basic code is done exporting the file to FILE.xls, I
want VB to open the file so it can be viewed and edited onscreen.

If at all possible I'd additionally want to Access to run some
formatting of the Excel file through visual basic.

Aug 9 '07 #2

P: n/a
Jim
Thanks...that worked!

Aug 10 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.