CODEWITHSUNDEEP
vbaexcelvbscript
SeagullWardy
SeagullWardy

Reputation: 187

Windows Script to open Excel file and run macro

Sorry I am very new to VBA so am learning as I go along. I have tried to search for an answer to this but everything I find seems to think that the macro will be in the file I want to open.

Once a month I get a file that contains some data. Up to now I had to look at the data then do a VLOOKUP to add some additional data before saving it.

I have now written a VBA macro to add the additional information I want and it works as intended. However what I would like to do is have a Windows script that will open the file I receive, run the macro I have written, then save the file as a csv.

Can anyone point me in the right direction? If needed my macro is below

Sub AddBandInfo()
Dim i As Long
For i = 2 To 50000
Dim band As String, result As Double
band = Range("B" & i).Value
If band = "A" Then result = 1144.02
If band = "B" Then result = 1334.7
If band = "C" Then result = 1525.36
If band = "D" Then result = 1716.04
If band = "E" Then result = 2097.38
If band = "F" Then result = 2478.72
If band = "G" Then result = 2860.08
If band = "H" Then result = 3432.08
If band = "" Then result = 0.01
Range("C" & i).Value = result
Next i

Dim r As Long
For r = Sheet1.UsedRange.Rows.Count To 1 Step -1
If Cells(r, "C") = "0.01" Then
    Sheet1.Rows(r).EntireRow.Delete
End If
Next

End Sub

Thanks

Upvotes: 0

Views: 2746

Answers (2)

user6432984
user6432984

Reputation:

We can improve AddBandInfo speed and performance by

  • Toggling Application.ScreenUpdating
  • Looping from the last row to the first row (doing this allows use to delete the rows as we go)

If 50000 isn't the actual last row use

For i = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1

Public Sub AddBandInfo()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 50000 To 2 Step -1
        Dim band As String, result As Double
        band = Range("B" & i).Value
        Select Case Cells(i, 2).Value
            Case "A"
                Range("C" & i).Value = 1144.02
            Case "B"
                Range("C" & i).Value = 1334.7
            Case "C"
                Range("C" & i).Value = 1525.36
            Case "D"
                Range("C" & i).Value = 1716.04
            Case "E"
                Range("C" & i).Value = 2097.38
            Case "F"
                Range("C" & i).Value = 2478.72
            Case "G"
                Range("C" & i).Value = 2860.08
            Case "H"
                Range("C" & i).Value = 3432.08
            Case Else
                Rows(i).EntireRow.Delete
        End Select
    Next
    Application.ScreenUpdating = True
End Sub

Assuming AddBandInfo is saved in C:\somedirectory\somefile.xlsx and that you want a vbscript to open the Workbook and then run AddBandInfo:

Const WorkBookPath = "C:\somedirectory\somefile.xlsx"
Dim xlApplication, xlWorkbook

Set xlApplication = CreateObject("Excel.Application")
Set xlWorkbook = xlApplication.Workbooks.Open(WorkBookPath)
xlApplication.Run "'" & xlWorkbook.Name & "'!AddBandInfo()"

xlWorkbook.Save , False
xlApplication.Quit

Set xlApplication = Nothing

Upvotes: 0

Tom K.
Tom K.

Reputation: 1042

Create a .txt-file and rename it to .vbs.

Start your code with:

Set objXLS = CreateObject("Excel.Application")
Set myWkbk = objXLS.Application.Workbooks.Open("C:\mypath\myfile.xlsx")
Set myWksh = myWkbk.Worksheets("yoursheet")
With myWksh

'your
'code
'here

End With

Set objXLS = Nothing

All done. :)

Upvotes: 0

Related Questions