Copy & rename a excel file to a sub-folder with a name entered in a forms control (1 Viewer)

Punice

Registered User.
Local time
Today, 12:50
Joined
May 10, 2010
Messages
135
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
 

Rx_

Nothing In Moderation
Local time
Today, 10:50
Joined
Oct 22, 2009
Messages
2,803
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"
 

Punice

Registered User.
Local time
Today, 12:50
Joined
May 10, 2010
Messages
135
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

Registered User.
Local time
Today, 12:50
Joined
May 10, 2010
Messages
135
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?
 

Users who are viewing this thread

Top Bottom