vba to mkdir, copy xls file to it & xfr form field contents to cells of xls file

[Punice]

:banghead:I submitted this problem to the 'Forms' forum and received several responses. None of those suggestions worked. So, I'm appealing to the VBA code experts for assistance.

By clicking on a Access 2007 form's control (mkmybidder), I want the on-click vba code to:
1. Create a new folder (C_Name) obtained from the form's 'C_Name' field;
2. Copy the 'model' file (path is 'C:\MyBidder\model.xlsx') to the new 'C_Name' folder;
3. Next, automatically transfer the contents of named form fields to designated cell locations on the model.xlsx file, without destroying the structure & data on that file.
4. Then, save & rename the copied 'model' xlsx file to 'C_Name_Roofr.xlsx';
5. Finally, open the file for reviewing. (I know how to save it ha ha 8.1.

BTW: VBA code to 'mkdir', only, does not function on my computers (Win 8.1 or XP);
however, it does work using the DOS command 'mkdir'. Also, the ' Microsoft Excel Object Library' reference is marked. Not aware of other 'marks' that may be required.

Here is my code, incorporating my requirements into example code from JHB of 'forms':

Private Sub mkmybidder_Click()
Dim appExcel As Excel.Application
Dim lngLastDataRow As Long

MkDir (Me.Bidder)
FileCopy "C:\Access programmer\Client.xls", (Me.Bidder) & "\C_Name Roof.xlsx"
Set appExcel = CreateObject("Excel.Application")
With appExcel
.Visible = True
.UserControl = True
With .Workbooks.Open((Me.Bidder) & "\C_Name Roof.xlsx")
lngLastDataRow = .Worksheets("Sheet1").Cells.SpecialCells(xlCellTyp eLastCell).Row
.Worksheets("Sheet1").Range("I2" & CStr(lngLastDataRow + 1)) = Me.C_Name
.Worksheets("Sheet1").Range("I3" & CStr(lngLastDataRow + 1)) = Me.Address
.Worksheets("Sheet1").Range("I4" & CStr(lngLastDataRow + 1)) = Me.City
.Worksheets("Sheet1").Range("I5" & CStr(lngLastDataRow + 1)) = Me.Zip
.Worksheets("Sheet1").Range("I7" & CStr(lngLastDataRow + 1)) = Me.Address
.Worksheets("Sheet1").Range("I8" & CStr(lngLastDataRow + 1)) = Me.Phone_No
.Worksheets("Sheet1").Range("I9" & CStr(lngLastDataRow + 1)) = Me.Work_No
.Worksheets("Sheet1").Range("N2" & CStr(lngLastDataRow + 1)) = Me.Cust_Date
.Worksheets("Sheet1").Range("N4" & CStr(lngLastDataRow + 1)) = Me.Cust_No
End With
End With
appExcel.WindowState = xlMaximized
Set appExcel = Nothing
End Sub

Most likely, I've interpreted JHBs example incorrectly. Tnx in advance for caring to help me.

 

[Cronk]

Re: vba to mkdir, copy xls file to it & xfr form field contents to cells of xls file

Mkdir will create a folder in an existing folder. If the parent folder(s) do not exist, you need to create them first.

Where is your code failing? That is, on which line and what is the error message?

Incidentally, if the last row is 100 then your code would be inserting values in cells I2100, I3100, I4100 etc. Is that what you want?

 

[Rx_]

Re: vba to mkdir, copy xls file to it & xfr form field contents to cells of xls file

The other items I have referenced are: OLE Automation (System32\stdole2.tlb), Microsoft Office 14.0 Access Database Engine Object, and Microsoft Visual Basic for Applications Extensibility 5.3

Like a Tosh.O video, we will break down your many questions. I have time for one. You can create the variables. Pasted is working code:
60 UserLogin = Environ("username")
70 UserPath = "X:\Regulatory\Database Reports\" & UserLogin & "\NutsAndBolts" ' folder location for a report "nuts and bolts'
80 strNewReportPath = UserPath
100 DirName = strNewReportPath
110 If Dir(DirName, vbDirectory) = "" Then
120 If MsgBox("Is it OK to create a new folder in X:\Regulatory\Database Reports\" & UserLogin & "\NutsAndBolts? (recommended yes)", vbOKCancel) = vbOK Then
130 DirName = UserPath
140 MkDir DirName
150 Err.Clear
170 Else
180 MsgBox "Create new folder cancelled. Folder not created.", vbOKOnly, "Report Cancelled, must allow folder to be created"
190 Exit Function
200 End If
210 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
230 End If '
240 If Err.Number <> 0 Then
260 MsgBox "Network path problem needs to be resolved " & Err.Description, vbOKOnly, "Network path to My Documents not found"
270 Err.Raise 3580, "ExcelReports", "Code module"
280 End If

240 If Err.Number <> 0 Then
260 MsgBox "Network path problem needs to be resolved " & Err.Description, vbOKOnly, "Network path to My Documents not found"
270 Err.Raise 3580, "ExcelReports", "Code module"
280 End If

Set the references and lets see if the make directory can be used.

 

[Punice]

Re: vba to mkdir, copy xls file to it & xfr form field contents to cells of xls file

Refs were marked as suggested; code seems to compile error free; last row is 'N67'.
Another problem: Debug doesn't work (ie, F8, etc), with/without 'allow special keys' picked. #$%@#

Here is my revised code from your help. It doesn't do any thing.
Private Sub mkmybidder_Click()
Dim appExcel As Excel.Application
Dim lngLastDataRow As Long

MkDir (Me.Bidder)
FileCopy "C:\MyBidder\Bidder.xlsx", (Me.Bidder) & "\C_Name Roof.xlsx"
Set appExcel = CreateObject("Excel.Application")
With appExcel
.Visible = True
.UserControl = True
With .Workbooks.Open((Me.Bidder) & "\C_Name Roof.xlsx")
.Worksheets("Sheet1").Range("I2") = Me.C_Name
.Worksheets("Sheet1").Range("I3") = Me.Address
.Worksheets("Sheet1").Range("I4") = Me.City
.Worksheets("Sheet1").Range("I5") = Me.Zip
.Worksheets("Sheet1").Range("I7") = Me.Address
.Worksheets("Sheet1").Range("I8") = Me.Phone_No
.Worksheets("Sheet1").Range("I9") = Me.Work_No
.Worksheets("Sheet1").Range("N2") = Me.Cust_Date
.Worksheets("Sheet1").Range("N4") = Me.Cust_No
End With
End With
appExcel.WindowState = xlMaximized
Set appExcel = Nothing
End Sub

 

[Cronk]

Re: vba to mkdir, copy xls file to it & xfr form field contents to cells of xls file

It would be easier to enable special keys while you are developing. Otherwise you can use Debug | Step Into from the menu in the VBA editor.

When you say the code "doesn't do anything", does the code execute? Is the directory created and the file copied?

You could set a break point near the start of the code (F9 or Debug|Toggle Break point and then step through the code.