CODEWITHSUNDEEP
vbams-access
Suduwudu
Suduwudu

Reputation: 29

Make variable folder in VBA

Currently I have a button in a form in access that runs a VBA. This VBA makes a folder and then exports a file from access db into an excel file in that folder. The code is this:

MkDir CurrentProject.Path & "\W46"
Dim outputlot3mah As String
outputlot3mah = CurrentProject.Path & "\" & "W46" & "\" & "Lot03_MAHs.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Lot03_MAHs", outputlot3mah, True

Is there a way that when the user clicks the button, he can enter a name and that will be the name of the folder? So for example if he chooses W78 as the name, the folder is named W78 and the file is entered in that folder.

So then it would be something like this

Dim getFolderName As String
MkDir CurrentProject.Path & getFolderName
Dim outputlot3mah As String
outputlot3mah = CurrentProject.Path & "\" & getFolderName & "\" & "Lot03_MAHs.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Lot03_MAHs", outputlot3mah, True

I'm new to VBA so I don't know if this is possible. Thank you!

Upvotes: 0

Views: 298

Answers (1)

Luis Curado
Luis Curado

Reputation: 756

you can use inputbox to get a string from user.

something like this

Dim getFolderName As String

getFolderName = InputBox("Give me the name of the folder")

MkDir CurrentProject.Path & getFolderName
Dim outputlot3mah As String
outputlot3mah = CurrentProject.Path & "\" & getFolderName & "\" & "Lot03_MAHs.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Lot03_MAHs", outputlot3mah, True

you have also to have some error control:

  • verify if user hit cancel

  • check if the folder exists

  • create the folder if it doenst exist

Upvotes: 2

Related Questions