Renaming a file using vba Results

Hi,

I am sort of a beginner here... I have a report that is set to print using PDFCreator: it automatically names the file as a time date stamp. I want to rename the file to include form data in its filename and move it to another directory. Here is what my code looks like:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim stDocName As String
Dim Directory As String
Dim Newdir As String
Dim Filename As String
Dim Newname As String
Dim oldfile As String
Dim newfille As String
Dim Dossier As String

stDocName = "Dossier privé - Confirmation"
DoCmd.OpenReport stDocName, acNormal
Dossier = [Dossier]
Directory = "C:adjudexdocumentstemp"
Newdir = "C:adjudexdocuments"
Filename = Directory & Dir("C:adjudexdocumentstemp", vbNormal)
Newname = Dossier & "Dossier_privé_Confirmation.pdf"
Name Filename As Newdir & Newname

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

When the code is done, the file is in its original name and place, but it somehow created a directory: C:adjudexdocumentsDossier_privé_Confirmation.pd f

I do not understand what I am doing wrong. Could someone out there please help me?

Thank you in advance.

Hello everyone,

I have been trying to find a solution to this problem for over a month by searching this forum in addition to other sources.


I am having trouble with the Name function in VBA - when the oldfilename + newfilename are clearly defined I have no problems with it. When I try to include wildcards with the name function then I run into all sorts of problems.


I want to rename a file that usually has the following format.

G0998189.US-BOE-SB-CA-054

The name changes from month to month so I am trying to use some sort of wildcard to rename it in a specific format (say test.txt). This is my code:

Quote: Dim FileName As String
Dim NewFileName As String

FileName = "C:Documents and SettingsMy DocumentstestG0998189.US-BOE-SB-CA*.*"

NewFileName = "C:Documents and SettingsMy Documentstesttest.txt"

Name FileName As NewFileName

End Sub Please note that the directory that contains the source file doesn’t include any other files – there is only one file in that folder in other words.

I would really appreciate any help.

Thank you in advance

I need to make a VBA Code to rename a file using only a section of the name.
The problem is that the first part of the name change any day and I need to ignore this part and rename the file.

The second part of the filename ".0651.TXT" don't change.

I need to do:

RENAME C.temp*.0651.TXT , C:tempAUDIT.0651.TXT

How can I make this code?

Thank's for support.
Regards

Giovanni Roi

Hi,

I have a process of loading a file to a temporary table and then appending the relevant records to another table.

Is there a way I can use VBA to either rename the file or ideally move it to another folder?

Regards
H.

I regularly import flatfiles from our network in order dissect, compare and produce reports. The report generator that produces these flat files only create files with a .lis extension. Access only seems to accept .txt extensions. Renaming the original flat file is doable but it does not allow me to import and process it through a macro. I tried using vba to import the file with a .lis extension but it still doesn't work. I have to manually rename the file on the network and then run the macro.

Any help will be greatly appreciated.

Hi Guys,

I have been asked by my employers to create a document retention system.

They recieved two documents from the customer which are scanned in as two separate PDF files. The PDF files are stored on the scanner units memory. The scanned documents are accessed from a web interface. The PDF will be downloaded and then saved to a folder. The user will then link the file in the database by entering the file path into a field. (how?) VBA code will be executed to rename the file, move the file from the original path and dump it in a central folder. A shellexecute script will be used to open the files from within access. (this is covered) So how do I go about llinking the files? I have never used access as a 'document server' before. Is there a way to have a file upload window where the user can navigate to the file? I can do the renaming bit all fine, just need to actually get the files path!!

Any help would be greatly apreciated.

Cheers,
Adam

I am new to VBA. I am exporting using the importexport text - export delimited in a macro and would like to either prompt the user for the name of the file - or use the form field name that is limiting the query as the filename. The users are doing hundreds of these and having to rename the file in between each.

Thanks.

I have an application which will import text files that appear in a folder on the server, I want it to import all of the files it finds (will be sequentially numbered file.001, file.002 etc) and then move them to another folder called 'done', I can work out everything except the actual moving of the files.

I know I could use FILECOPY and KILL, but my concern is that the multitasking aspects of VBA might cause the KILL operation to be started before the FILECOPY operation has finished, especially if the file is a large one. (incidentally, is this he kind of thing that can be overcome by inserting DoEvents in between the two operations?)

Is there a way of moving a file in a single operation? Actually, renaming the files would also be acceptable, if this is easier.

Thanks in advance

Mike

I need to rename files contained in a folder.
The new filenames are held in a table along
with the corresponding old filenames.
Example

Table1
Field1(OldFileName) 100026578.jpg
Field2(NewFileName) Pic6589ST.jpg

Is there any VBA code that can use the
NewFileName to rename the files in the
folder on the hard disk

Many thanks

Hi,
I have a database of engineering drawings with fields such as drawing number, drawing name etc. I also have the corresponding image files but the filenames are different to the drawing names.

I have code (thanks to ghudson!) that will open the image file and put the filename into a text box.

What I would like to know is
- is it possible to open this file and change the filename of the image itself to the drqwing name stored in the textbox?
- if so could anyone give me pointers on where to find out about this.

Thanks,
RCurtin

P.S. Is there an API anywhere. For some reason I dont have the VBA help files on my computer and the object browser doesn't really tell you how to use the objects and associated procedures.

Hi:

Is there a way to import text files with file extensions other than .txt either with VBA or SQL or?

I have several files to import daily and they do not use the TXT extension. I would rather not have to rename each file before importing.

Thanks

I've exhausted myself using functions such as GetObject, etc. For a method that will let me know whether another application is currently open or not. I need to know this because when we pass data via vba from Access to project at times I will get, "This file is read only, click save as to save the file and rename it" This is NOT a network issue in that the file has read/write access to me and several other users. The problem is other users may be viewing this file while someone else is sending data. So I need a method() / function() which will tell me if a file is not in use / in use.

Thanks team,
Jon

I am writing code to open a .csv file in access and then save this as an .xls file. I can open the file using shell but haven't been able to manipulate excel. I've tried the AppActive method but it doesn't seem to change the focus to the excel file. Here is the code I'm struggling with.

csvfile = Shell("excel.exe" & " " & TargetFile, vbNormalFocus)

AppActivate csvfile
SendKeys "%F"
SendKeys "A"
SendKeys excelfile 'rename the file as an .xls
SendKeys "%F"
SendKeys "x"

Any help is appreciated.

Ok, I have been searching the internet for a possible solution to this issue, but have yet to find a valid solution that will work for me. I am using Application.FollowHyperlink to open files located on our network that are associated with a request in my database. This functionality works great for .pdf, .doc, .xls, .ppt, etc. files but it returns the Runtime Error 490 Cannot open the specified file when a MS 2007 format file is attempted to be opened (.xslx, .docx, etc.). In addition, .msg files also cannot be opened via this method, but I have a work around for this and told my users to deal with it (they respond well to this sometimes).

So, does anyone know of a SIMPLE way to be able to open any type of file via VBA code in an Access 2007 database? I am trying to stay away from having to enter application specific code to handle the different files that do not open using Application.FollowHyperlink command. Below is the code that I am currently using which does work as noted above for most files:

Code: Private Sub Open_Btn_Click() On Error Goto ErrorHandler Dim PPCAttachDir As String Dim PPCAttachFile As String DIm PPCOpenFile As String PPCAttachDIR = gUDrive & "" & Me.RequestID PPCAttachFile = Me.AttachFile PPCOpenFile = PPCAttachDir & "" & PPCAttachFile Debug.Print PPCOpenFile If PPCAttachFile Like "*.msg" Then MsgBox "Outlook Message files cannot be opened from within the database. You must open the email " & _ "file directly from the attachment folder." & vbcrlf & vbcrlf & "The attachment folder will " & _ "be opened for you upon closing this message box.", vbokonly+vbInformation, "Cannot open .MSG files" Application.FollowHyperLink PPCAttachDir ElseIf Len(Dir(PPCOpenFile, vbNormal)) = 0 Then MsgBox "The file " & PPCAttachfile & " has been moved, renamed, or deleted. Please click on " & _ "the folder link on the Request Form to verify the file name and update it as needed." _ , vbOkOnly + vbExclamation, "File Not Found" Goto ExitHere Else Application.FollowHyperLink PPCOpenFile, , True End If ExitHere: Exit Sub ErrorHandler: Call UnexpectedERror(Err.Number, Err.Description, Err.Source) Resume ExitHere EndSub By the way, the gUDrive is the UNC path to our network drive which is stored as a Global Variable, not the mapped drive letter on the user's PC. This is to ensure that if a user has modified their mappings from the norm, it will still locate the file because of the UNC path being used. It also allows me a simple update to the database if the UNC path is ever changed to a new server (recently happened), so I only need to make one change in the database as opposed to many!!

Hi
I am trying to extact the file name and use it as a variable in Xl. I am importing a text file in here and since it is a text file, the sheet shall have the same name as the imported text file. It is then saved as the same name with .xls extension. What I am trying to do is to extract the name from the filename and use the thing to rename tha sheets and add the sheets. But I get error in Find location.

Can you guide me out. Below is the code for reference.

Sub Getfilename()

Dim zyy As String, zab As Integer, zxx As String, zzz As String

zyy = ActiveWorkbook.Name
zxx = ActiveWorkbook.Path
MsgBox " Workbook path is " & zxx
MsgBox " Current Filename is " & zyy
zzz = Left("zyy", Find(".", "zyy") - 1)


Sheets("zyy").Select
Sheets("zyy").Name = "Sheet1"

'And this is the VBA code to Add new Sheet to the open file.
Do Until zab <= 4

Sheets.Add
zab = zab + 1
Loop
End Sub

Excel 2007, .XLSX file.

If I issue a Create Table sql command from VBA using an Excel connection string

CREATE TABLE Customers


I end up with a worksheet named "Customers" (instead of Sheet1). Perfect. However, the problem is that I now have a worksheet that I renamed manually (using my keyboard) from "Sheet1" to "Customers" - and currently Vbscript isn't recognizing the table name at all. I've tried

UPDATE Sheet1 SET....
UPDATE [Sheet1$] SET....
UPDATE Customers SET...
UPDATE [Customers$] SET...

This is what I get:

Error: The Microsoft Office Access database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.


Error: The Microsoft Office Access database engine could not find the object 'Customers'. Make sure the object exists and that you spell its name and the path name correctly.

I even went to Developer > Properties and changed the sheet name in the Properties window to "Customers" but still get the same error...


	Code:
	 
Private Sub Command6_Click()
   'Create a Catalog object
 
   Dim con As New ADODB.Connection
   con.Open CurrentProject.AccessConnection
 
   Dim Cat As New ADOX.Catalog
   Cat.ActiveConnection = con
 
 Dim oldn As String
 Dim newn As String
 Dim tbln As String
 
tbln = "test"
oldn = "dog101"
newn = "dog"
   'Create a table object
 
   Dim Tbl As ADOX.Table
   Set Tbl = New ADOX.Table
 
   Set Tbl = Cat.Tables(tbln)
   Tbl.Columns(oldn).Name = newn
 
   Set Cat = Nothing
   Set Tbl = Nothing
 
End Sub



I get run-time error 3251

object or provider is not capable of performing requested operation
on this line

Tbl.Columns(oldn).Name = newn

using access 2007 w accdb file type

anyone got an idea of what can fix this?

I created a database on my personal laptop that has Access 2010. There are several reports that are generated via a parameter query and with the use of macros, they are then emailed in PDF format when I click a button on the main form I use. However, the version of Access I have at work is 2003 so these buttons no longer work. I created new buttons to preview the report so that I can save as a PDF and email to those who require them. There are a lot more steps doing it this way and I know there has to be a better way.

Below is the VBA code generated by Access.


	Code:
	Private Sub ReportCS_Click()
On Error GoTo Err_ReportCS_Click

    Dim stDocName As String

    stDocName = "rpt_LTL Shipments for CS"
    DoCmd.OpenReport stDocName, acPreview

Exit_ReportCS_Click:
    Exit Sub

Err_ReportCS_Click:
    MsgBox Err.Description
    Resume Exit_ReportCS_Click
    
End Sub

This is a shipping report and when I click the button, I enter the date in as "1/27" and the report shows the shipping for that day. Now I need to right click and print to a PDF format and the name always default to the name of the report in Access. What I would like it to do is always save in the same folder on my hard drive and rename it to "CS - Shipping Report 2012.01.27.pdf" whereas the date would change to the one entered in the parameter.

Is this something that would be easy to code? Or can anyone suggest other options?

Hopefully I explained this well enough.

Hello All

This is my first post on this forum.
I have found some very useful information here to complete this task, but being a noob to VBA, can't put the pieces together.
(Sorry for the wording, English is only my third language.)

Your help is much appreciated.

_What I would like to do_

Based on values of a form create a directory and copy renamed files there, then open a folder and open a file.

*form*
value1
value2
value3

*create directory*
x:directoriesvalue1value2value3

If it exists, stop and give error message.

*create subdirectories*
x:directoriesvalue1value2value3dir1
x:directoriesvalue1value2value3dir2

*copy files*
x:file_locationfilename1.ext to x:directoriesvalue1value2value3renamed_filena me1.ext
x:file_locationfilename2.ext to x:directoriesvalue1value2value3renamed_filena me2.ext

*open folder*
x:directoriesvalue1value2value3

*open_file*
x:directoriesvalue1value2value3renamed_filena me1.ext


_What I have so far_

-for creating the directory value1:
Private Sub Command337_Click()
strPath = "X:directories" & "" & value1
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
-Need help with the error message.

-for creating the directory value2:
Private Sub Command337_Click()
strPath = "X:directories" & "" & value1 & value2
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
-Need help with the error message.

-for creating the directory value3:
Private Sub Command337_Click()
strPath = "X:directories" & "" & value1 & value2 & value3
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
-Need help with the error message.

FileCopy "x:file_locationfilename1.ext" ????
-Stuck here

-open folder
Shell "explorer.exe " & strPath, vbNormalFocus

-open file
-Stuck here


Thank you

Here is my question:

1.) I have a database (duh!!) which we use in our showroom for order entry. We then back up the db to CDRW and bring it to our wharehouse (weekly) to use for creating purchase orders, delivery tickets etc... The copy we use at the wharehouse only has one field that we change in the entire db. When i get the two copies together the showroom copy will have additional orders not in the wharehouse copy. I have some vba that checks in the wharehouse copy for orders that are marked 'complete' (the one field we modify) and then updates the showroom copy. (Lost yet?)

2.) I have a DOS batch file that after the above updating of the Showroom db renames the existing Wharehouse db as .bk1 and replaces the Wharehouse db with the Showroom db (thus becoming the Wharehouse db). (My head hurts!!)

Shocking as it may seem, this works great for our purposes.

But, what i would love to be able to do is incorporate step 1 above into the DOS batch file and thus update and replace the Wharehouse db in one easy step.

Now the real question... can i call the module that updates the 'Complete' field in the Showroom db from within the batch file.

Well at this point i would guess you are either laughing :-) or your head hurts like mine!

Thanks for taking the time to at least read this!

D'oh!!


Not finding an answer? Try a Google search.