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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export from Access TO Excel    
 
   
rexwrx
post Aug 1 2006, 01:26 AM
Post#1



Posts: 9
Joined: 13-June 06



Hi,
I've got a simple form with a button that On_click should "Export" the contents of a table in the access d/b.
here's my code:
Private Sub cmbExpExcel_Click()
On Error GoTo Err_ExportQuery_Click
Dim strFilename As String

strFilename = " D:\Output\TestCases_" & Format(Now(), "mmyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, 8, "tbl_test", strFilename
Exit_ExportQuery_Click:
On Error GoTo 0
Exit Sub
Err_ExportQuery_Click:
Beep
MsgBox Err.Description, , "Error: " & Err.Number _
& " in file"
Resume Exit_ExportQuery_Click


End Sub
QUESTION:
Oget this error: "Error 3436 in File. Failure creating File".
Can anyone suggests what is going on?? Do I need to create the file first?
I want VBA to automatically create the Excel File since i'm appending the Date at the end of the File_NAME.
Thanks!!
Go to the top of the page
 
bome
post Aug 1 2006, 02:04 AM
Post#2


UtterAccess VIP
Posts: 1,644
Joined: 24-May 02
From: Sweden


Perhaps the file already exists or is opened in Excel? Also check that the folder exists and that you have write permeission in that folder.
_____________
Bo Melin
Edited by: bome on Tue Aug 1 3:07:49 EDT 2006.
Go to the top of the page
 
rexwrx
post Aug 1 2006, 02:07 AM
Post#3



Posts: 9
Joined: 13-June 06



File should not have existed since it will unique with the mm/dd at teh end of it. but yes, i've checked there is no file in the folder.
lso, i've got full access. and tried teh root directory.
Go to the top of the page
 
rexwrx
post Aug 1 2006, 02:14 AM
Post#4



Posts: 9
Joined: 13-June 06



Ok OK.
How the problem seems to be that I HAVE to create the FOLDER D:\Output manually first.
Then this works.
My question is then, how can I create the folder in VBA?? <
Go to the top of the page
 
bome
post Aug 1 2006, 02:21 AM
Post#5


UtterAccess VIP
Posts: 1,644
Joined: 24-May 02
From: Sweden


I use this API function

Public Declare Function apiMakeSureDirectoryPathExists Lib "Imagehlp.dll" _
Alias "MakeSureDirectoryPathExists" _
(ByVal DirPath As String) As Long

Call it like this

blnResult = apiMakeSureDirectoryPathExists("D:\Output\")

It will create any number of folder levels. Please note, always end the path with \ .
_________
Bo Melin

Edited by: bome on Tue Aug 1 3:22:44 EDT 2006.
Go to the top of the page
 
kapeller
post Aug 1 2006, 02:47 AM
Post#6


UtterAccess VIP
Posts: 2,848
Joined: 31-December 02
From: Melbourne Australia Down Under


Hi !!!!!
Have a look at this working example
It may help.
Cheers!!!!!!
Lou
Go to the top of the page
 
rexwrx
post Aug 1 2006, 06:02 PM
Post#7



Posts: 9
Joined: 13-June 06



THANKS ALL! wink.gif
I'll try them out and let you guys know again!
Go to the top of the page
 
rexwrx
post Aug 1 2006, 06:09 PM
Post#8



Posts: 9
Joined: 13-June 06



cool, Bo, it works.
That does this blnResult = apiMakeSureDirectoryPathExists("D:\Output\") do actually? i know it's to call the API function.
Lou, that's awesome! i'll give it a go with your code as well as i like the calendar option!!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 05:07 AM