By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,124 Online
Bytes IT Community

Home Questions Articles Browse Topics Latest Top Members FAQ

home > topics > microsoft access / vba > questions > after exporting access query to xls how do i use vb to auto-rename the file?
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

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

SteHawk85
P: 34
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFSPFile_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4.     DoCmd.OpenQuery "qryMakeFSPAttainmentImportTable", acViewNormal, acReadOnly
  5.     DoCmd.RunCommand acCmdOutputToExcel
  6.  
  7.  
  8. CleanUpAndExit:
  9.     Exit Sub
  10.  
  11. ErrorHandler:
  12.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  13.     Resume CleanUpAndExit
  14. End Sub
  15.  
Any help is greatly appreciated

Ste
May 14 '12 #1

✓ answered by 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

Share this Question
Share on Google+
16 Replies
NeoPa
Expert Mod 15k+
P: 31,261
To rename a file from VBA use the Name statement.
May 14 '12 #2
zmbd
Expert Mod 5K+
P: 5,287
to close your query:

Expand|Select|Wrap|Line Numbers
  1. 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
May 14 '12 #3
SteHawk85
P: 34
thanks guys will give it a go and let you know!
May 15 '12 #4
SteHawk85
P: 34
have got it to close but can't figure out the name statement! any1 have an example i can look at?
May 18 '12 #5
zmbd
Expert Mod 5K+
P: 5,287
@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
May 18 '12 #6
NeoPa
Expert Mod 15k+
P: 31,261
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 :
Expand|Select|Wrap|Line Numbers
  1. Name "C:\My File.xls" As "C:\MyFile.XLS"
Usage for moving a file would be :
Expand|Select|Wrap|Line Numbers
  1. 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.
May 18 '12 #7
zmbd
Expert Mod 5K+
P: 5,287
Are you renameing the file after using the transferworksheet method or something else?

the following code worked in access 2010:

Expand|Select|Wrap|Line Numbers
  1. Sub ztest()
  2. Dim OldName as string, NewName as string
  3. '
  4. ' Define file names.
  5. OldName = "C:\Documents and Settings\zmbd\My Documents\newtextfile.txt"
  6. NewName = "C:\Documents and Settings\zmbd\My Documents\renamed_newtextfile.txt" 
  7. '
  8. 'file system command
  9. ' Rename file.
  10. Name OldName As NewName
  11. End Sub
Also tried this on a workbook and a jpeg file with success.

-z
-z
May 18 '12 #8
SteHawk85
P: 34
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFSPFile_Click()
  2. On Error GoTo ErrorHandler
  3. Dim OldName As String, NewName As String
  4.  
  5.     'Define file name
  6.     OldName = "C:\Documents and Settings\hawkinss\My Documents\qryMakeFSPAttainmentImportTable.xls"
  7.     NewName = "C:\Documents and Settings\hawkinss\My Documents\FSP Attainment Import.xls"
  8.  
  9.     DoCmd.OpenQuery "qryMakeFSPAttainmentImportTable", acViewNormal, acReadOnly
  10.     DoCmd.RunCommand acCmdOutputToExcel
  11.     Name OldName As NewName
  12.     DoCmd.Close acQuery, "qryMakeFSPAttainmentImportTable"
  13.  
  14. CleanUpAndExit:
  15.     Exit Sub
  16.  
  17. ErrorHandler:
  18.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  19.     Resume CleanUpAndExit
  20. End Sub

this one is using NeoPa's

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFSPFile_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4.     DoCmd.OpenQuery "qryMakeFSPAttainmentImportTable", acViewNormal, acReadOnly
  5.     DoCmd.RunCommand acCmdOutputToExcel
  6.     Name "C:\Documents and Settings\hawkinss\My Documents\qryMakeFSPAttainmentImportTable.xls" As "C:\Documents and Settings\hawkinss\My Documents\FSP Attainment Import.xls"
  7.     DoCmd.Close acQuery, "qryMakeFSPAttainmentImportTable"
  8.  
  9. CleanUpAndExit:
  10.     Exit Sub
  11.  
  12. ErrorHandler:
  13.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  14.     Resume CleanUpAndExit
  15. End Sub
I realise they are essentially the same code just thought I would post both.

Once again thanks for all your help

Ste
May 21 '12 #9
NeoPa
Expert Mod 15k+
P: 31,261
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.
May 21 '12 #10
SteHawk85
P: 34
nope, same error coming through! Is it because the excel spreadsheet it produces is still open?
May 21 '12 #11
zmbd
Expert Mod 5K+
P: 5,287
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
May 21 '12 #12
SteHawk85
P: 34
i have had a look and it makes sense, i will edit my code and let you know! thanks!
May 21 '12 #13
SteHawk85
P: 34
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!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdKS1File_Click()
  2. On Error GoTo ErrorHandler
  3. Dim fileName, qryout As String
  4. fileName = "C:\Documents and Settings\hawkinss\My Documents\qryFSP Attainment Import.xls"
  5. qryout = "Query1"
  6. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qryout, fileName, True
  7.  
  8. CleanUpAndExit:
  9.     Exit Sub
  10.  
  11. ErrorHandler:
  12.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  13.     Resume CleanUpAndExit
  14. 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?
May 21 '12 #14
SteHawk85
P: 34
I have found the right type of Spreadsheet, its Excel9! will try and let you know
May 21 '12 #15
SteHawk85
P: 34
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFSPFile_Click()
  2.  
  3. On Error GoTo ErrorHandler
  4. Dim fileName, qryout As String
  5. fileName = "C:\Documents and Settings\hawkinss\My Documents\FSP Attainment Import.xls"
  6. qryout = "qryFSP Attainment Import"
  7. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qryout, fileName, True
  8.  
  9. CleanUpAndExit:
  10.     Exit Sub
  11.  
  12. ErrorHandler:
  13.     Call MsgBox("An error has been encountered" & vbCrLf & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Number: " & Err.Number, , "Error Deleting Record")
  14.     Resume CleanUpAndExit
  15.  
  16.  
  17.  
  18. 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?
May 21 '12 #16
zmbd
Expert Mod 5K+
P: 5,287
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
May 21 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.

Question stats

  • viewed: 4833
  • replies: 16
  • date asked: May 14 '12
BYTES.COM © 2019 About Bytes | Advertise on Bytes | Privacy Policy | Manage Cookies
Sitemap | Microsoft Access / VBA Answers Sitemap | Microsoft Access / VBA Insights Sitemap

Follow us to get the Latest Bytes Updates