By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,219 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,219 IT Pros & Developers. It's quick & easy.

VBA Script to Change an Excel Filename

100+
P: 121
I have an Access database I am importing records into from an Excel file. I currently have set it up by creating a linked table to the Excel sheet. But I am not sure that is the best way to do what I'm trying to achieve. So, I have two questions:


1) The user will be copying all the Excel files into one folder (this is just how I currently envision it). Is there a way to copy records from all those files in the folder, without having to have the user name them all the same.

For example: If there is a file named "Test.xls" and "Test2.xls" can I get it to add both those files' records to the table?

2) Can I rename the files that have been imported do that I can mae a check so that I know which files hvae been already processed. If I can't do the task i described in #1, then I could do this one one file at a time. This is the more important of the two

For example: Once he has imported "Test.xls" - can I change it to "Test - Imported.xls"

Thanks in advance for any help
Mar 28 '07 #1
Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,392
Subscribing.
Mar 29 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
You can use the following code but move the files to a new folder after they are imported rather than renaming them. This code is set behind a command button called cmdImportExcelFiles.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdImportExcelFiles_Click()
  2. Dim strFile As String 'Filename
  3. Dim strFileList() As String 'File Array
  4. Dim intFile As Integer 'File Number
  5. Dim filename As String
  6. Dim path1 As String, path2 As String
  7.  
  8.     DoCmd.SetWarnings False ' Turn off database warnings
  9.  
  10.    ' set the full path names for the folders
  11.     path1 = "C:\FolderName\" ' folder with list of files
  12.     path2 = "C:\OtherFolderName\" ' folder to move files to
  13.  
  14.     'Loop through the folder & build file list
  15.     strFile = Dir(path1 & "*.xls")
  16.     While strFile <> ""
  17.          'add files to the list
  18.         intFile = intFile + 1
  19.         ReDim Preserve strFileList(1 To intFile)
  20.         strFileList(intFile) = strFile
  21.         strFile = Dir()
  22.     Wend
  23.      'see if any files were found
  24.     If intFile = 0 Then
  25.         MsgBox "No files found"
  26.         Exit Sub
  27.     End If
  28.  
  29.     ' cycle through the list of files
  30.     For intFile = 1 To UBound(strFileList)
  31.         filename = path1 & strFileList(intFile)
  32.  
  33.        ' import data to table (True means excel file has field names)
  34.        DoCmd.TransferSpreadsheet acImport, , "TableName", filename, True
  35.        moveFile filename, path2 & strFileList(intFile)
  36.    Next intFile
  37.  
  38.    DoCmd.SetWarnings True
  39.  
  40. End Sub
  41.  
  42. ' procedure to move file from one folder to another
  43. Public Sub moveFile(fromPath As String, toPath As String)
  44.     copyOverFile fromPath, toPath ' call file copy procedure
  45.     delFile fromPath ' call file delete procedure
  46. End Sub
  47.  
  48. ' procedure to copy file from one location to another
  49. Public Sub copyOverFile(fromPath As String, toPath As String)
  50. Dim fs As Object
  51.  
  52.     Set fs = CreateObject("Scripting.FileSystemObject")
  53.     fs.CopyFile fromPath, toPath, True
  54.  
  55. End Sub
  56.  
  57. ' procedure to delete a file
  58. Public Sub delFile(fromPath As String)
  59. Dim fs As Object
  60.  
  61.     Set fs = CreateObject("Scripting.FileSystemObject")
  62.     fs.DeleteFile fromPath, True
  63.  
  64. End Sub
  65.  
Mary
Mar 29 '07 #3

100+
P: 121
Thanks so much - worked like a charm!!!
Apr 3 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks so much - worked like a charm!!!
You're welcome.
Apr 3 '07 #5

P: 13
I have a script for copying files between servers.I use MoveFile.The problem is I'm getting the error:

Microsoft VBScript runtime error '800a004c'

Path not found

Could you tell me what's causing the error?

Thanks!
Aug 29 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
I have a script for copying files between servers.I use MoveFile.The problem is I'm getting the error:

Microsoft VBScript runtime error '800a004c'

Path not found

Could you tell me what's causing the error?

Thanks!
You are not giving the full and correct directory path to the file. Have a look at line 11 and 12 in the previous code
Aug 29 '07 #7

P: 13
You are not giving the full and correct directory path to the file. Have a look at line 11 and 12 in the previous code
My prblem is I want to copy files between 2 servers on the same network.I specify the destination server's name like:\\server\..\...The other server is running my asp script.The method is working on the asp server , but when I try to copy the file on the other server,I get that error message.
Sep 3 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
My prblem is I want to copy files between 2 servers on the same network.I specify the destination server's name like:\\server\..\...The other server is running my asp script.The method is working on the asp server , but when I try to copy the file on the other server,I get that error message.
This should work

FileCopy "\\ServerName\ShareName\Test.Txt", "\\ServerName2\ShareName\Test.Txt"
Sep 3 '07 #9

P: 13
This should work

FileCopy "\\ServerName\ShareName\Test.Txt", "\\ServerName2\ShareName\Test.Txt"
I used that too...I'm getting the following error:

Microsoft VBScript runtime error '800a0035'

File not found
Sep 4 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
I used that too...I'm getting the following error:

Microsoft VBScript runtime error '800a0035'

File not found
Then your problem is not with the VB code. Have your checked the network connections to make sure you have write priviliages on the server?
Sep 4 '07 #11

P: 13
Then your problem is not with the VB code. Have your checked the network connections to make sure you have write priviliages on the server?
The person in charge of the server told me I should set a connection to the destination server.How do I do that?
Sep 7 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
I would guess that this is not a database issue. He probably means that you have to add the server to your directory list.
Sep 7 '07 #13

Post your reply

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