Instructing Dos to take a formula from a cell within excel and excecuteing it

[proditneg]

Hi,

I am having a difficulty on writing a command within excel so that a set of formulas on a given column is excecuted in dos.

this is the formula which i want to be excecuted from within excel:

md "D:\location\folder name " & xcopy "S:\location\filename" "D:\location\folder name" & Rename "D:\location\filename" "new filename"

this formula runs accros the column depending on the number of entries.

If anyone can advice that would be extremley helpful as im stuck at this point.

Cheers

 

[RichardMGreen]

AFAIK make directory is available in Excel VBA anyway.
I'm not sure about doing the copying/renaming without opening each file (assuming they are all Excel files) and resaving them. If they are all Excel files, then this would be straightforward if tedious/time-consuming.

 

[Ruddles]

You can't do it directly in the Excel worksheet, you need to pass those values to a VBA routine to build the appropriate command and execute it.

Incidentally VBA knows how to make directories, copy files and rename files, so there's no need to use DOS commands.

 

[proditneg]

Thanks for youe replies,

Unfortunately i am not experienced in VBA at all, hence i have managed to use a couple of excel formulas to come up with a DOS command within a particular cell, which i want it excecuted from dos.

The command implies the following instructions:

Create a directory (name and location taken from a cell)
Copy file from a directory to this directory (all info on the same cell)
Rename file just copied to a new file name (info on the same cell)

I a few words all this set of commands resides on a single cell, and each subsequent cell on the column has the same set of commands but with different location and/or filenames. I want this set of commands to be excecuted from within excel, rather than having to open dos and copy and paste those commands into dos and excecute.

 

[Ruddles]

Assuming your worksheet looks like this:-

<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=227><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6875" width=188><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2798760 height=20 width=39></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 141pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=188>

A​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>c:\folder1\</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>

2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>c:\folder2\temp.txt</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>

3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>temp99.txt</TD></TR></TBODY></TABLE>

This code should do the job:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub CopyRename()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim sFolder As String
  Dim sFilename As String
  Dim sNewFilename As String
  
  sFolder = ActiveSheet.Range("A1").Value
  If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
  If Dir(sFolder, vbDirectory) = "" Then MkDir sFolder
  
  sFilename = ActiveSheet.Range("A2").Value
  If Dir(sFilename) = "" Then
    MsgBox sFilename & " does not exist!", vbOKOnly + vbExclamation
    Exit Sub
  End If
  
  sNewFilename = ActiveSheet.Range("A3").Value
  If Dir(sFolder & sNewFilename) <> "" Then
    MsgBox sFolder & sNewFilename & " already exists!", vbOKOnly + vbExclamation
    Exit Sub
  End If
    
  FileCopy sFilename, sFolder & sNewFilename
  If Dir(sFolder & sNewFilename) = "" Then
    MsgBox sFilename & " copy failed!", vbOKOnly + vbExclamation
    Exit Sub
  End If
  
  MsgBox sFilename & " copied to " & sFolder & sNewFilename, vbOKOnly + vbInformation
  [/FONT]
[FONT=Fixedsys]End Sub
[/FONT]

To install this code, in new worksheet, press Alt-F11 to open Microsoft Visual Basic, press Ctrl-R to view the Project Explorer, then
go Insert > Module. A new 'standard' code module will appear under Modules, probably called Module1. You might need to click the + symbol against Modules to expand the group.

Double-click the name of this new module to open it. Remove any code you find in the code window (probably only the words Option Explicit, if anything) and paste my code in its place - everything from Option Explicit down to and including End Sub.

Return to your worksheet. Make sure you have a new folder name in A1, an existing filename in A2 and a the name you want for the copied file in A3. (You can change all these once you've got everything working okay.) The code tries to detect simple error conditions like no backslash on the end of the folder name, the source file not existing, the target file already existing, etc.

Finally click Developer > Macros, select the macro called CopyRename from the dialog box and click Run. Check that the new folder has been created and that the correct file has been copied with the correct new name.

If you have any problems, this code comes with a 24-hour warranty!

 

[proditneg]

Hi there Rudles,

The code you have provided will most likely do the job it implies, but because i want to excecute a formula (Location column U) whose information is based on the results of the information gathered from a number of cells, the only thing to work in this case will be a way to instruct dos to excecute that formula. I have attached a screen of the column which contains the commands.

What i need is to find a way through which excel will look into a range of cells (column U), copies information from those cells, communicates with dos and instructs dos to excecute that information.

Cheers

 

[Ruddles]

Your data was truncated - I couldn't see it all - however I think I can see enough.

1) You must use VBA - there's no way to do it directly from Excel. If there was, that would make a complete mockery of system security.

2) Your cell contains several commands joined together: DOS will not understand them when they're presented in that way. You must pass them to DOS individually, one at a time.

3) In any case DOS is not a good way of achieving this: if you have to use VBA - which you do - then using native VBA commands is neater.

I don't know what has led you to building that string in column U as you have done. You have pursued a path which will not lead you to a solution.

Instead of gathering together the values in those cells, you need to pass them to VBA to handle.

I can rewrite my VBA to handle your column U if you can show me the formula which produces those values.

 

[taurean]

Take a look at the thread below (post #7) where I had posted an alternative way of doing things. Looks to me you are after something of that sort.

http://www.vbaexpress.com/forum/showthread.php?t=36352

The only intention behind giving this thread is you can see the file uploaded which may be of some use for you.

 

[proditneg]

Ruddles,

This is the formula which gathers info from a number of cells.

=B4&C4&D4&E4&F4&G4&H4&I4&J4&K4&L4&N4&O4&P4&Q4&R4&S4&T4

The end result which i then manually paste into dos is;

md "D:\location\folder name " & xcopy "S:\location\filename" "D:\location\folder name" & Rename "D:\location\filename" "new filename"

Just to let you know, the set of formulas on the column U, work fine when i paste them i dos , all at once. There is no need to paste them one by one. Let me know if you need more info.

Cheers

 

[taurean]

Did you take a look at the link I have posted?