After exporting Access query to xls how do I use VB to auto-rename the file?

[SteHawk85]

Nearly finished with my first VBA project and its looking quite good and people seem impressed. However, I have one little problem that I am sure could easily be cleaned up. At the min I have a button that opens a query and then instantly opens it in excel (very similar to the ‘analyse in excel’ pre-set button).

However, my problem is when the 'export' to excel takes place the file automatically takes the name of the query e.g. 'qryMakeFSPAtta inmentImportTab le'. However, after they have finished updating the data in the excel spreadsheet it needs to be imported back in under the name 'FSP Attainment Import'. I realise I could just tell people to rename the file, but you know and I know this would never be the case and I would get constant email's flying my way saying the import function doesn’t work. I know the code I have used isn’t the best as the query is opened when the button is pressed and stays open afterwards; this is a bit of a pain too. I have pasted my code below

Private Sub cmdFSPFile_Click()
On Error GoTo ErrorHandler

    DoCmd.OpenQuery "qryMakeFSPAttainmentImportTable", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdOutputToExcel
    
    
CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
    Resume CleanUpAndExit
End Sub

Any help is greatly appreciated

Ste

[NeoPa]

To rename a file from VBA use the Name statement.

[zmbd]

to close your query:

DoCmd.Close acQuery, "qryMakeFSPAttainmentImportTable"

You might also take a look at transfering a sheet instead:

http://msdn.microsoft.com/en-us/library/bb214134%28v=office.12%29.aspx

-z

[SteHawk85]

thanks guys will give it a go and let you know!

[SteHawk85]

have got it to close but can't figure out the name statement! any1 have an example i can look at?

[zmbd]

@SteHawk85:
Sorry... I don't exactly understand your question.
Are you inquiring about the "DoCmd.Clos e .... "?
The online help should hopefully answer that...
OR
Are you inquiring about the transfersheet method?
If so, I have an example that I provided in another thread...

http://bytes.com/topic/access/answers/940229-updating-access-using-excel-spreadsheets-used-distribute-access-info

Post#4; look at the code-block starting with "Sub zj_excel_query2 sheet_1()" That has an example of the access to excel.
One thing to note, if you have a workbook with the same name as given in the method AND the query is the same, the new data WILL overwrite the old data. Other thing, the constant I used is "acSpreadsheetT ypeExcel12Xml" that is, I beleave, for Office 2007/2010 if you need a different format check the online help for the correct constant to use.

There is a second code-block with the excel to access... read the thread before using it... that code is actually the VERY first time I've pulled data into a database using this method.

In both cases... I consider these to be crude code as there are no error traps, U.I, etc... they are merely intended to start people, hopefully :), on the right path.

... and my spell checker isn't working today, sigh. o.O

-z

[NeoPa]

SteHawk85:
have got it to close but can't figure out the name statement! any1 have an example i can look at?

Usage for a simple rename would be :

Name "C:\My File.xls" As "C:\MyFile.XLS"

Usage for moving a file would be :

Name "C:\My File.xls" As "C:\OtherFolder\MyFile.XLS"

Type and select "Name As", press F1 and then select the VBA option for the full details ;-)

It's a VBA statement rather than a procedure call. Inbuilt in the language itself.

[zmbd]

Are you renameing the file after using the transferworkshe et method or something else?

the following code worked in access 2010:

Sub ztest()
Dim OldName as string, NewName as string
'
' Define file names.
OldName = "C:\Documents and Settings\zmbd\My Documents\newtextfile.txt"
NewName = "C:\Documents and Settings\zmbd\My Documents\renamed_newtextfile.txt" 
'
'file system command
' Rename file.
Name OldName As NewName
End Sub

Also tried this on a workbook and a jpeg file with success.

-z
-z

[SteHawk85]

Hi Guys

Thanks for both your responses, I have tried both and keep getting the same error, Path/File Access Error Number: 75. After having a quick look on the web I think this means the file I am trying to save to won’t allow access for renaming the file. So I check my access rights to this file, which is my own 'My Documents' file and not surprisingly I have full rights. Is this because of the system I work on? Can I do anything to changes this? It’s rather frustrating as I have just this one thing to do and I am finished. Do you think renaming the query that is getting exported to the file name I need it to be would be easier? Below are both sets of code I wrote using both your answers.

this one is using zmbd's

Private Sub cmdFSPFile_Click()
On Error GoTo ErrorHandler
Dim OldName As String, NewName As String

    'Define file name
    OldName = "C:\Documents and Settings\hawkinss\My Documents\qryMakeFSPAttainmentImportTable.xls"
    NewName = "C:\Documents and Settings\hawkinss\My Documents\FSP Attainment Import.xls"

    DoCmd.OpenQuery "qryMakeFSPAttainmentImportTable", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdOutputToExcel
    Name OldName As NewName
    DoCmd.Close acQuery, "qryMakeFSPAttainmentImportTable"
    
CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
    Resume CleanUpAndExit
End Sub

this one is using NeoPa's

Private Sub cmdFSPFile_Click()
On Error GoTo ErrorHandler

    DoCmd.OpenQuery "qryMakeFSPAttainmentImportTable", acViewNormal, acReadOnly
    DoCmd.RunCommand acCmdOutputToExcel
    Name "C:\Documents and Settings\hawkinss\My Documents\qryMakeFSPAttainmentImportTable.xls" As "C:\Documents and Settings\hawkinss\My Documents\FSP Attainment Import.xls"
    DoCmd.Close acQuery, "qryMakeFSPAttainmentImportTable"
    
CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
    Resume CleanUpAndExit
End Sub

I realise they are essentially the same code just thought I would post both.

Once again thanks for all your help

Ste

[NeoPa]

It won't allow a rename (from anywhere) if it's still open, and it's still open until line #7. If you close the query before renaming the resultant file I suspect that would work for you.

[SteHawk85]

nope, same error coming through! Is it because the excel spreadsheet it produces is still open?

[zmbd]

SetHawk95:
Sorry, I didn't catch that the query was still open.

However, you might want to use the transfersheet method.
This method should allow you to directly create the workbook using the path and file name you want... the worksheet in the workbook will have the queryname.

Please take a look at post #4 in that thread:

http://bytes.com/topic/access/answers/940229-updating-access-using-excel-spreadsheets-used-distribute-access-info

Just noticed that line-5 in the first code block that the endsub isn't on its on line; however, that should be an easy fix.

You should also look at the microsoft website (#3 in this thread)

Something to note, when I use this method, the output file usually WILL overwrite any exsisting file and data!

-z

[SteHawk85]

i have had a look and it makes sense, i will edit my code and let you know! thanks!

[SteHawk85]

I have tried the code, and the file that is exported is still called 'qryFSP Attainment Import' instead of 'FSP Attainment Import' here is the code!

Private Sub cmdKS1File_Click()
On Error GoTo ErrorHandler
Dim fileName, qryout As String
fileName = "C:\Documents and Settings\hawkinss\My Documents\qryFSP Attainment Import.xls"
qryout = "Query1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qryout, fileName, True

CleanUpAndExit:
    Exit Sub

ErrorHandler:
    Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
    Resume CleanUpAndExit
End Sub

then when i have tried your import code I am getting an error on the type of Spreadsheet 'acSpreadsheetT ypeExcel12Xml' this is causing the error.

any ideas?

[SteHawk85]

I have found the right type of Spreadsheet, its Excel9! will try and let you know