0

I am trying to automate a script that I use for PowerShell. When I get a request, instead of editing the script line by line with required information, I would like to create an Excel spreadsheet, input the data required, and populate my script with those variables in the correct location.

From a high level view with limited programming experience, this does not seem overly complicated but I cannot figure it out.

I am able to do bits and pieces of what I am trying to accomplish, but not everything together. Below is step by step what I want to accomplish.

  1. Input data into Cells B10 through B16
  2. Store each cell into an individual variable
  3. Hit the command button which will take the variables and print/overwrite a file already created.

I have a specific file path that I need to use.

Here is the code that I have already, that I see will copy the cells and open my PowerShell module.

Public Sub CommandButton1_Click()

Dim fileName As Variant

Dim Azure As Workbook

Dim wsSource As Worksheet

Set Azure = ActiveWorkbook

Set wsSource = ActiveSheet

'Copy range on original sheet
wsSource.Range("B10:B16").Copy

fileName = Shell("powershell_ise.exe ""c:\Azure\AZURE_1.ps1""", vbNormalFocus)

End Sub

I can see that the above code doesn't accomplish what I need to do. However, I can see that I can use the button, copy the cells, and open the required file.

What I need to do is take each cell and place at a certain spot in my script and overwrite the previous file.

halfer
  • 19,824
  • 17
  • 99
  • 186
heyitsmeiseeyou
  • 1
  • You might want to look into printing a txt file: http://www.homeandlearn.org/write_to_a_text_file.html – Luuklag Jun 09 '17 at 14:47
  • I have, and I got it to somewhat work. I could print to a text file, but not in the format I need. When I printed to text, the text file would show all the cells text rather than the specific cells of B10 through B16. Also, I was only able to do it once. Each time after I got a runtime error saying file already existed. This would be pointless if I print to a text file then have to copy over to powershell to run my script? I am tyring to take each specific cell and place in string, print the string to my powershell file which overwrites old file. – heyitsmeiseeyou Jun 09 '17 at 14:52
  • Take a look at [this question](https://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba) (which was the first non-ad result from a Google search for "VBA write to text file"). Where it does a single `WriteLine`, you'll need multiple lines to create the text of your PowerShell script. You can reference `wsSource.Range("B" & RowNum).Value` (where `RowNum` is an `Int` that represents the row in column B) to get access to the contents of the individual cells. You don't want to use `.Copy` in this case. – FreeMan Jun 09 '17 at 15:02
  • You'll also have to delete the file yourself in Explorer before re-running your macro (unless you include a check for an existing file and delete it in the macro -- but start with just deleting it yourself first). – PeterT Jun 09 '17 at 15:04
  • Hey Guys, thank you so much!!! Highly Appreciated! Freeman thank you, I can now print what I want and in the format I want. My last question getting the data from the cells in the correct location in the script. I see what Freeman posted with using:wsSource.Range("B" & RowNum).Value Couldn't I just store each cell to a variable and when I write the text, use those variables data? – heyitsmeiseeyou Jun 09 '17 at 15:22

0 Answers0