Main Area and Open Discussion > General Software Discussion

from excel to a folder structure

(1/1)

kalos:
hello!

is there possibility to click a button in excel and do this:

create a folder under a specific directory structure in a website (there is ftp access, maybe ssh) based on the data in some cells

or maybe an external program can do that better? ie. read specific cells and create a relevant folder structure in a web directory? which solution would be more reliable?

thanks!

AbteriX:
Such can be done with Office VBA, if you happen to code in Visual Basic (Script)....

There are Excel commands in VBA to iterate over cells,
and there are "standard" VBA/VBS commands to create folders and files via FSO (FileSystem Object)


 

kalos:
thanks! where is the reference of these commands?

AbteriX:
In the help.

Launch Excel > choose > "Developer > Visual Basic" > Press F1 > Choose "Visual Basic for Applications Language Reference"

Also search there for "Filesystem".

- - -

But I would rather use G00gle to find some help in special Excel/Script forum.
"excel vba get cell value"
"excel vba create folder"


Examples:

- in Excel Visual Basic, insert a Module

- in Module1 insert:
Sub Macro1()
    myVariable = Range("A1").Value
    MsgBox myVariable
End Sub

- in excel, write something in cell A1 and execute this macro.




- next add FSO function:
- edit Macro1() to:
Sub Macro1()
    myVariable = Range("A1").Value
    MsgBox myVariable
   
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CreateFolder "C:\Temp\" + myVariable
    MsgBox "Folder created: C:\Temp\" + myVariable
End Sub


- in excel, write something in cell A1 and execute this macro to create that folder.


Of course you have to add many more code, also for error handling, e.g. if path is valid, or if folder not already exist.

Sub Macro1()
    myVariable = Range("A1").Value
    MsgBox myVariable
    myFolder = "C:\Temp\" + myVariable
    Set fso = CreateObject("Scripting.FileSystemObject")
    If (fso.folderExists(myFolder)) Then
        MsgBox "Folder already exist: " + myFolder
    Else
        fso.CreateFolder myFolder
        MsgBox "Folder created: " + myFolder
    End If
End Sub


- - -

The other way around would be to use Visual Basic Script or PowerShell
to access the Excel Object, get the cell value, and invoke FSO to create the folder.

Navigation

[0] Message Index