Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • December 10, 2016, 08:21:11 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: from excel to a folder structure  (Read 941 times)

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,477
    • View Profile
    • Donate to Member
from excel to a folder structure
« on: March 18, 2015, 04:04:52 AM »
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

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 1,115
    • View Profile
    • Donate to Member
Re: from excel to a folder structure
« Reply #1 on: March 18, 2015, 06:54:31 AM »
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

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,477
    • View Profile
    • Donate to Member
Re: from excel to a folder structure
« Reply #2 on: March 19, 2015, 04:42:53 AM »
thanks! where is the reference of these commands?

AbteriX

  • Charter Honorary Member
  • Joined in 2005
  • ***
  • Posts: 1,115
    • View Profile
    • Donate to Member
Re: from excel to a folder structure
« Reply #3 on: March 19, 2015, 07:02:09 AM »
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.