Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-05-2014, 09:04 AM   #1
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 103
Thanks: 46
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Red face Copy & rename a excel file to a sub-folder with a name entered in a forms control

I am still trying to copy an Excel file from a parent folder to a sub-folder, that this code creates, and name the copied file with the name used to name the sub-folder, using the name entered in a control field (named 'TheDirName) of an Access 2007 form. Here is my VBA, which names the copied file "TheDirName.xlsx.:

Private Sub MakeDir_Click()
Dim appExcel As Excel.Application
Dim lngLastDataRow As Long
Dim Folder_Path As String

'Create path for customer folder & files
strFolder_Path = "C:\R11Bidder13\" & (Me.TheDirName)

If Dir(Me.TheDirName, vbDirectory) = "" Then
If MsgBox("OK to create folder!", vbOKCancel) = vbOK Then
MkDir strFolder_Path
'The name entered into the 'TheDirName' window of the "Customers"_ form.

Else
MsgBox "Create folder cancelled. Folder not created."
Exit Sub
End If
Else
MsgBox "The folder already exists..." & Chr(10) & "Please check the_ directories using Windows Explorer.", vbOKOnly
Exit Sub
End If
Response = MsgBox(TheDirName, vbOKOnly)

'Copy the file 'Bidder.xls' to the newly created folder.
FileCopy "C:\R11Bidder13\Bidder.xlsx", strFolder_Path &- "\TheDirName.xlsx"
End Sub

Punice is offline   Reply With Quote
Old 01-05-2014, 11:02 AM   #2
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Re: Copy & rename a excel file to a sub-folder with a name entered in a forms control

Here during a blizzard on Sunday, so this will be very brief.
OBJXL is an Excel Application object variable
Code:
      ' <<<<<<<< FYI   if no records returned to excel, stop report process >>>>>>>>>>>
240         DirName = strNewReportPath
250             If Dir(DirName, vbDirectory) = "" Then
260                   If MsgBox("Is it OK to create a new folder in X:\Regulatory\Regulatory Database Reports\" & UserLogin & "\Audit (recommended yes)", vbOKCancel) = vbOK Then
270                       DirName = UserPath
280                       MkDir DirName
290                       Err.Clear
310               Else
320                   MsgBox "Create new folder cancelled. Folder not created.", vbOKOnly, "Report Cancelled, must allow folder to be created"
330                       Exit Function
340               End If
350             Else
                    'MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly
                    ' if it exist, don't bother letting the user know
370             End If      '
380     If Err.Number <> 0 Then
400         MsgBox "Network path problem needs to be resolved " & Err.Description, vbOKOnly, "Network path to My Documents not found"
410         Err.Raise 3580, "ExcelReports", "Code module"
420     End If
' modify Excel as needed
Code:
2950        If CurrentUser <> "Admin" And CurrentUser <> "SomeUSERNAME" And blnTestMode = False Then ' checking stuff
2960            ObjXL.ActiveWorkbook.SaveAs FileName:=strNewReportPath
2970        End If

2980        strSaveAsFileName = strNewReportPath & "\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Now()) & "-" & Minute(Now()) & "-" & " Service Person for Audit" & "  " & Area_String_For_Report_Name(ID_Area) & ".xlsx"
3000        ObjXL.ActiveWorkbook.SaveAs FileName:=strSaveAsFileName
3010        ObjXL.Visible = False
3020        ObjXL.Quit
            
            '    Name the Report and let the user know of the location -----
3030        msgString = UserPath & "\" & Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & "-" & Hour(Now()) & "-" & Minute(Now()) & "-" & " Well API-Node ID" & "  " & Area_String_For_Report_Name(ID_Area) & ".xlsx"
            
3040        MsgBox "Excel report saved at : " & msgString, vbOKOnly, "Please Open This File Location for Your Report"
__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 01-09-2014, 09:46 AM   #3
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 103
Thanks: 46
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: Copy & rename a excel file to a sub-folder with a name entered in a forms control

With my code, I can create a directory with the name (eg., Jones) that I enter into a window on a form (eg., "C:\R11Roofing13\Jones"). The name of the window on the form, where 'Jones' is entered is 'TheDirName'.

What I need is the additional code to [1] copy an excel file, named 'Bidder.xlsx' that is located in "C:\R11Roofing") folder to the 'C:\R11Roofing13\Jones' folder that my code creates, [2] rename this file in it to 'Jones Bidder.xlsx' and [3] open 'Jones Bidder.xlsx'?
I know, from previous help from this forum, how to transfer the data from the form to cells in the 'Jones Bidder.xlsx' file. I want to do the transfer thing before opening the xlsx file, like transfer the name entered into the 'town' window of the form to the designated cell (C3) on the newly created excel file.

Punice is offline   Reply With Quote
Old 01-11-2014, 09:08 AM   #4
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 103
Thanks: 46
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: Copy & rename a excel file to a sub-folder with a name entered in a forms control

I'm discouraged that none of the 103 persons who looked at my request for assistance with my vba problem (for the past 2 months plus) are not able to 'fix' it.

Am I not phrasing my request clearly?

Punice is offline   Reply With Quote
Reply

Tags
copy , form control value , rename file

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy pdf file and rename with recordset ID byTimber General 2 04-14-2012 10:30 PM
copy file on same folder with diffrent name bee55 Modules & VBA 1 10-26-2011 10:42 PM
Rename and move file to an existing folder PISCES Modules & VBA 3 12-18-2006 09:00 AM
Copy File and Rename it infinitx General 2 01-16-2005 05:30 AM
Copy text file to other folder Prince Modules & VBA 1 10-09-2003 02:28 PM




All times are GMT -8. The time now is 04:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World