Need code to mkdir,copy xlxs file to it, etc

[Punice]

By clicking on a form's control, I want the on-click vba code to:
1. Create a new folder named 'L_Name' from the form's 'L_Name' field;
2. Next, copy a 'model' xlsx file in C: to that folder & save it as
'L_Name Roof'.xlsx;
3. Then transfer contents of form fields 'Name, Address, Location' to the 'L_Name Roof'.xlsx cells 'h1, h2, h3' without destroying the structure & data in the file and open the file for reviewing. (I know how to save it ha ha.) I Read a zillion forums, etc. and can't even get the 'mkdir' code to work in Access 2007 with OS win8 or 8.1. However, two weeks spent learning. So, once again I'm asking for help. Tnx in advance.

 

[JHB]

I don't know what zillion forums you have read, but if you Google Make dir+Ms-Access, Make file+Ms-Access and Append data to an excel file+ms-access, you'll get the answer in the 1-4 first hits on all 3 question.

Open your form, place a text control on it, call it "TheDirName", create also a button on the form, and put the below code in the click event for button, (remember to change "PutInYourPathHere" to the path where the file is copied from, remember also to set the references to the Excel Object Library):

  Dim appExcel As Excel.Application
  Dim lngLastDataRow As Long
  
  MkDir (Me.TheDirName)
  FileCopy "C:\Access programmer\Client.xls", Me.TheDirName & "\L_Name Roof.xlsx"
  Set appExcel = CreateObject("Excel.Application")
  With appExcel
    .Visible = True
    .UserControl = True
     With .Workbooks.Open(Me.TheDirName & "\L_Name Roof.xlsx")
       lngLastDataRow = .Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
       .Worksheets("Sheet1").Range("A" & CStr(lngLastDataRow + 1)) = Me.Name 
       .Worksheets("Sheet1").Range("B" & CStr(lngLastDataRow + 1)) = Me.Address
       .Worksheets("Sheet1").Range("C" & CStr(lngLastDataRow + 1)) = Me.Location
     End With
  End With
  appExcel.WindowState = xlMaximized
  Set appExcel = Nothing

If you get any error the show the error number and the error message here.

 

[Punice]

Here is my code for a control (button) on my form called 'mkmybidder'. The xlsx file with the 9 fields that I want to fill from the fields on the form are in 'C:\MyBidder' which contains the unmodified file 'Bidder.xlsx'. I know that my problem is easy for someone who understands all of the vba jargon and that I'm short on experience in that area. Tnx for your patience and help.

Code:
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(xlCellTypeLastCell).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

 

[JHB]

And does your code not work?
Any error number or error message, (and in which line in the code does it pop up)?

 

[Punice]

When I try to compile, I get "Compile Error: User-defined type not defined" for
"Dim appExcel As Excel.Applications" in the code that you provided. If I 'comment'
that code out and compile the code, I don't get any error messages.
Wondering if the code is correct for the conditions that I stated and if not, what the code is that will work. tnx again for your prompt reply and willingness to help me.

 

[jdraw]

I think this has an extra s at the end (spelling mistake)

Dim appExcel As Excel.Applications

 

[Punice]

My code doesn't have the 's'. The 's' in my previous reply was a typo. I surely wish that was the solution to my problem. Hell, I can't even get simple 'mkdir' create a folder from Access 2007 in Win 8.1. Tnx for caring, though.

 

[JHB]

When I try to compile, I get "Compile Error: User-defined type not defined" for
"Dim appExcel As Excel.Applications" in the code that you provided. If I 'comment'

Have you set the reference to the Microsoft Excel Object Library as I mention earlier?
Open the code window, choose "Tools" -> References and mark the Microsoft Excel Object Library in the list.

 

[Punice]

Did not know about the 'ref' setting requirement. Did that, which enabled the code to compile error free. However, It does not make a directory nor do anything towards my objective. tnx for your guidance and patience. Not being able to even have 'mkdir' function is perplexing. I've tried many of the suggested code that I've read and nadda. Is it a Widows 8 problem?

 

[David R]

Just as a sanity check, are you able to manually do all of the things you want? Windows will only let Access do on your behalf what you (user account currently logged in) can ordinarily do.

 

[JHB]

Did not know about the 'ref' setting requirement. Did that, which enabled the code to compile error free. However, It does not make a directory nor do anything towards my objective. tnx for your guidance and patience. Not being able to even have 'mkdir' function is perplexing. I've tried many of the suggested code that I've read and nadda. Is it a Widows 8 problem?

I have attached a small example for you, which run here by me on a Windows 8 system.
Open the only form in it and type in the drive and the directory name you want to create, then press the "Create Dir" button.
Check if you have the permission to create directories where you try to do it, like David R mention.

 

[Punice]

Oh my! when I unzipped the file, it opened as a 'php'. Then, when I 'clicked-on' that file it invoked my app that I use to create web pages (Eversoft's First Page 2006) and I couldn't get any other program to open it correctly via 'open with' selection. Sorry.

David: I can use Excel & Access to perform what I want to do ' ordinarily & manually'.

 

[Punice]

JHB....The 'php' file in the 'Client.zip' file that you sent will not open using any win 8.1 program or others that I 'googled' and downloaded that stated that they could do that.
Would you please advise or send the contents of the file as something else? tnx sum more.

 

[JHB]

JHB....The 'php' file in the 'Client.zip' file that you sent will not open using any win 8.1 program or others that I 'googled' and downloaded that stated that they could do that.
Would you please advise or send the contents of the file as something else? tnx sum more.

I've convert the database it to a MS-Access 2000 MDB-file, maybe it helps you.
The ZIP file content 2 files, an Excel file called "Client" and the database file called "MK_DIR_NEW".
Remember to change the directory to where you put the "Client" file in, it is in the code behind the button.

  FileCopy "[COLOR=Red][B]C:\Access programmer\Client.xls[/B][/COLOR]", Me.TheDirName & "\L_Name Roof.xls"

 

[Punice]

OK JHB, the sample code worked fine. It fooled me by saving the directory and xls file it created (and opened with my added data) in the 'user' folder.
I didn't expect that.
Now, that I am able to make a directory, I am attempting to get my code that I presented, initially, to work using what I've learned from your example. I think my current 'no workee' is related to the paths and names of things. So, won't bother you any more. Thank you and others for all that you have done to help me.