transferspreadsheet

O

odun

Registered User.
Local time
Today, 12:10
Joined
Apr 24, 2005
Messages
108
Hello all,

I have the following code that exports my access query into an excel spreadsheet, but it does not open the excel file, it only saves it in my folder, how do I make the excel file open from that folder?

Thanks for your help!

Code: 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryDetails", "C:\QueryDetail.xls", True

I have this other code that is supposed to open the excel file, but excel opens up without an actual spreadsheet opening up:

Dim Obj1 As Object
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryDetails", "C:\QueryDetail.xls", True
Set Obj1 = GetObject("C:\QueryDetail.xls")
Obj1.Application.Visible = True
 
I have a similar problem...I was able to create a macro Action-RunCommand and the Command is OutputToExcel. When I do this, it does open excel and you see the data there, but I'm not able to put it in a specific file location. And I don't know how to write the VB code. So maybe you know more about it than I do and can combine your way and my way and get what you need. If you do, can you post the answer so I can try it out?
 
Not tested at all, but:

Code: 
Sub ExportToExcelAndOpenIT()

Dim xlapp As Object 
Dim xlBook As Object 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryDetails", "C:\QueryDetail.xls", True
Set xlapp = CreateObject("Excel.Application") 
Set xlBook = xlapp.Workbooks.Open("C:\QueryDetail.xls")

Set xlBook = Nothing 
Set xlapp = Nothing 

End Sub

Be careful, the above will open another version of Excel if you have one open.

You can put the following Function into a module and test to see if Excel is open first:

Code: 
Private Const SW_HIDE = 0 
Private Const SW_SHOWNORMAL = 1 
Private Const SW_NORMAL = 1 
Private Const SW_SHOWMINIMIZED = 2 
Private Const SW_SHOWMAXIMIZED = 3 
Private Const SW_MAXIMIZE = 3 
Private Const SW_SHOWNOACTIVATE = 4 
Private Const SW_SHOW = 5 
Private Const SW_MINIMIZE = 6 
Private Const SW_SHOWMINNOACTIVE = 7 
Private Const SW_SHOWNA = 8 
Private Const SW_RESTORE = 9 
Private Const SW_SHOWDEFAULT = 10 
Private Const SW_MAX = 10 

Private Declare Function apiFindWindow Lib "user32" Alias _ 
"FindWindowA" (ByVal strClass As String, _ 
ByVal lpWindow As String) As Long 

Private Declare Function apiSendMessage Lib "user32" Alias _ 
"SendMessageA" (ByVal Hwnd As Long, ByVal Msg As Long, ByVal _ 
wParam As Long, lParam As Long) As Long 

Private Declare Function apiSetForegroundWindow Lib "user32" Alias _ 
"SetForegroundWindow" (ByVal Hwnd As Long) As Long 

Private Declare Function apiShowWindow Lib "user32" Alias _ 
"ShowWindow" (ByVal Hwnd As Long, ByVal nCmdShow As Long) As Long 

Private Declare Function apiIsIconic Lib "user32" Alias _ 
"IsIconic" (ByVal Hwnd As Long) As Long 

Function fIsAppRunning(ByVal strAppName As String, _ 
Optional fActivate As Boolean) As Boolean 
Dim lngH As Long, strClassName As String 
Dim lngX As Long, lngTmp As Long 
Const WM_USER = 1024 
On Local Error GoTo fIsAppRunning_Err 
fIsAppRunning = False 
Select Case LCase$(strAppName) 
Case "excel": strClassName = "XLMain" 
Case "word": strClassName = "OpusApp" 
Case "access": strClassName = "OMain" 
Case "powerpoint95": strClassName = "PP7FrameClass" 
Case "powerpoint97": strClassName = "PP97FrameClass" 
Case "notepad": strClassName = "NOTEPAD" 
Case "paintbrush": strClassName = "pbParent" 
Case "wordpad": strClassName = "WordPadClass" 
Case Else: strClassName = "" 
End Select 

If strClassName = "" Then 
lngH = apiFindWindow(vbNullString, strAppName) 
Else 
lngH = apiFindWindow(strClassName, vbNullString) 
End If 
If lngH <> 0 Then 
apiSendMessage lngH, WM_USER + 18, 0, 0 
lngX = apiIsIconic(lngH) 
If lngX <> 0 Then 
lngTmp = apiShowWindow(lngH, SW_SHOWNORMAL) 
End If 
If fActivate Then 
lngTmp = apiSetForegroundWindow(lngH) 
End If 
fIsAppRunning = True 
End If 
fIsAppRunning_Exit: 
Exit Function 
fIsAppRunning_Err: 
fIsAppRunning = False 
Resume fIsAppRunning_Exit 
End Function

(I got the code from www.mvps.org a few years ago and the link I have doesn't work so I can't credit it to anyone)
BTW it tests many other Microsoft App and is very useful.
Anyway, if you use this you can change:

Code: 
Set xlapp = CreateObject("Excel.Application")

to:

Code: 
If fIsAppRunning("Excel", True) Then 
    Set xlapp = GetObject(, "Excel.Application") 
    Else 
    Set xlapp = CreateObject("Excel.Application") 
    End If


Make for a tidier App
 
Last edited:
odun said:
Code: 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryDetails", "C:\QueryDetail.xls", True
Click to expand...
You have the "Range" value set to True when it should be False. Try this instead...
Code: 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourQuery", "C:\YourQuery.xls", False
Application.FollowHyperlink "C:\YourQuery.xls"

I prefer to use the OpenFile method that I have posted @ Browse [Find a directory or file]
 
thanks Ian. I tried the code, but no excel file comes up and then I ran it again and it says the file already exists, but can't find it.
 
If you will be kind enough to explain what you mean, that will be great, otherwise, please don't bother to help if you're not willing to help all the way.

Thanks again!
 
odun said:
If you will be kind enough to explain what you mean, that will be great, otherwise, please don't bother to help if you're not willing to help all the way.

Thanks again!
Click to expand...
:p

My first post in this thread has a solution to your problem.
 
Thanks, I tried that code, and I am getting error 3051 or something like that "the file already exists or you do not have permission to access the file"...so, not sure what is going on. But the code works perfectly on one computer but not on another computer, that's the dilema and both pcs have the same version of excel and access.
 
You need to test if the file exists and kill [delete] it if it does exist before you run the code to create the xl file. There is plenty of code floating around on how to do just that.
 
Try the following code:

File = "c:\temp.xls"

If Shell("excel.exe " & Chr(34) & File & Chr(34), 1) then
' Success
else
'Failure
Msgbox " error opening the file"
endif
 
Thanks Jatz,
I included the code you suggested, and it works great on one computer, but I am still getting error 3051, "the file already exists or you do not have permission to access the file", when I try it on another computer.
Would you suggest reinstalling excel on the computer that is giving me problems, would that solve the problem or could there be something that needs to be turned on or off?

Thanks again!
 
You must log in or register to reply here.

Similar threads

Gasman
OpenQuery and TransferSpreadsheet?
Replies
17
Views
517
The_Doc_Man
The_Doc_Man
M
Export 2 forms to same spreadsheet
Replies
7
Views
336
mounty76
M
B
  • Question Question
export and transpose problem
Replies
8
Views
379
Pat Hartman
P
J
Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel
Replies
5
Views
360
June7
June7
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
427
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom