Excel Help (VBA optimization)

Taramoor · August 2017

Okay, so I use Excel a lot for work, and generally I can find macros or tips to make macros or VBA scripts or what have you elsewhere on the internet, but I haven't had any luck yet and, well, I trust you people.

So here's the thing:

Sub UpdateApps()

Dim marketLaunch As String

SetCurrentDirectoryA "C:\"

marketLaunch = ActiveSheet.Range("V116").Text

If Worksheets("Command Sheet").Range("W2") = "0" Then
MsgBox "Please check your IP"
Exit Sub
Else
End If

CreateObject("wscript.shell").Run "C:\Windows\system32\cmd.exe /c" & "cd C:\Program Files (x86)\[Sorry, can't share this directory name because Work stuff]\bin && " & marketLaunch, 1, True

End Sub

This opens a command prompt and runs some code (Which I have on cell V116 on the active page). Part of the command string drops the results of that code into a text file in a predetermined directory.

So depending on another cell the result will be Pakistan.txt or Egypt.txt or etc.

Now, there are around 160 different variables that will be used to run this, pulled from a dropdown, and they are the names of each Sheet in my Workbook.

I created a second Sub that will open each of those potential text files and paste them into the appropriate tabs. So it opens Pakistan.txt and pastes it into the Pakistan sheet in my workbook, etc.

Sub UpdateList()

Dim customerBook As Workbook
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook

Application.DisplayAlerts = False 'Disable all the Alerts from excel
Application.ScreenUpdating = False

' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook

Set customerWorkbook = Application.Workbooks.Open("C:\Users\mooglealert\Desktop\HoldingPen\ConsoleApps\Argentina.txt")

' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets("Argentina")
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)

targetSheet.Range("A1", "A2000").Value = sourceSheet.Range("A1", "A2000").Value

' Close customer workbook
customerWorkbook.Close

Set customerWorkbook = Application.Workbooks.Open("C:\Users\mooglealert\Desktop\HoldingPen\ConsoleApps\Brazil.txt")

Set targetSheet = targetWorkbook.Worksheets("Brazil")
Set sourceSheet = customerWorkbook.Worksheets(1)

targetSheet.Range("A1", "A2000").Value = sourceSheet.Range("A1", "A2000").Value

' Close customer workbook
customerWorkbook.Close

It just repeats 16 times with the name of each text file. But it slows things down, sometimes the text files haven't been updated so it's wasting time, etc.

What I want to be able to do, is set the targetWorkbook.Worksheet based on Cell information, so I only need to press the button once to update a specific sheet with the information.

Unfortunately my google-fu only turns up results for using VBA to create a sheet with a name based on a cell. I'm sure the solution is a relatively simple one, but all of my VBA stuff is already so cobbled together that I'm at a loss.

Anybody out there know how to make this do what I want?

The Big Levinsky · August 2017

Turn the chunk of code that does the update into it's own subroutine that accepts the name of the target sheet as a paramter perhaps?

Sub UpdateList(strCountryName as String)

Dim customerWorkbook as Workbook

Application.DisplayAlerts = False 'Disable all the Alerts from excel'
Application.ScreenUpdating = False

Set customerWorkbook = Application.Workbooks.Open("C:\Users\mooglealert\Desktop\HoldingPen\ConsoleApps\" & strCountryName & ".txt")

Sheets(strCountryName).Range("A1", "A2000").Value = customerWorkbook.sheets(1).Range("A1", "A2000").Value

customerWorkbook.close

End Sub

Then you can call the same sub as needed:

UpdateList "Brazil"
UpdateList "Argentina"

Maybe make like some command buttons on a sheet that correspond to the countries so you can just click the one you want. Or, if if it's cell info, like, you click a button and you want the code to check the value of a cell to find the country name, you could try making a named range. In the formulas tab in the ribbon, there's a Name Manager you can create named ranges. To refer to them in code:

Workbook scoped names:

Thisworkbook.names("[name of your range]").RefersToRange.value

Sheet scoped names:

Sheets("[sheet name]").names("[name of your range]").RefersToRange.value

So, put it all together:

Sub DoUpdate
     UpdateList Thisworkbook.names("[name of your range]").RefersToRange.value
End Sub

Sub UpdateList(strCountryName as String)
     Dim customerWorkbook as Workbook

     Application.DisplayAlerts = False 'Disable all the Alerts from excel'
     Application.ScreenUpdating = False

     Set customerWorkbook = Application.Workbooks.Open("C:\Users\mooglealert\Desktop\HoldingPen\ConsoleApps\" & strCountryName & ".txt")

     Sheets(strCountryName).Range("A1", "A2000").Value = customerWorkbook.sheets(1).Range("A1", "A2000").Value

     customerWorkbook.close

End Sub

Pailryder · August 2017

I like the big levinksy's answer. the other option might be to check for a modified date on the files themselves and if its not today or within whatever date paramater you want then ignore those sheets?

Penny Arcade

Quick Links

Excel Help (VBA optimization)

Posts