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. 'qryMakeFSPAttainmentImportTable'. 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/libr...ice.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.Close .... "?
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/answer...te-access-info
Post#4; look at the code-block starting with "Sub zj_excel_query2sheet_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 "acSpreadsheetTypeExcel12Xml" 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 transferworksheet 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/answer...te-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 'acSpreadsheetTypeExcel12Xml' this is causing the error.

any ideas?

[SteHawk85]

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

[SteHawk85]

YYYYYYYYYYYYYEEEEEEEEEEESSSSSSSSSSSSSSSSS that has worked! In my current Manic state I had added it to the wrong button, however now I have put it where it is meant to be and finally it works in the right format with the right name! Here's the code

Private Sub cmdFSPFile_Click()
 
On Error GoTo ErrorHandler
Dim fileName, qryout As String
fileName = "C:\Documents and Settings\hawkinss\My Documents\FSP Attainment Import.xls"
qryout = "qryFSP Attainment Import"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, 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

Just got to see if the Import works now! If a new pupil's data is added to the sheet I will need an append query as well as an update correct? If so how do I do this so it adds the new data that doesn't exist in the table?

[zmbd]

Somthing to point out... that the imported data using this method may well have "type" issues, i.e. numbers imported as text etc.

NeoPa had the steps well laid out:

Essentially, for any individual worksheet, the process would have to include :

• Import the raw data in. Probably into a temporary table.
• Using this table update the original data in such a way that records from the temporary table and records from the original database are matched accurately and reliably. Complexity increases related to how many separate fields and possible updates are supported.
• If new records are required as well as updates then, as long as they are correctly and safely identified, these can also be handled in the previous step.
• Lose the temporary data/table.

So, yes, an append/update query is the way to go... the update query should add a new item to the table provided the new record doesn't violate something in the tables.

-z