CODEWITHSUNDEEP
excelvba
shadow2020
shadow2020

Reputation: 1351

How to adapt this to work with another worksheet?

How do I adapt this to work with another worksheet rather than the worksheet I have visible. 

If (IsEmpty(Cells(RowNum, ColumnNum).Value)) Then
    GoTo nextloop:
End If

I have moved parenthesis, tried including workbook name but I think I'm just not getting the syntax. I'm not very good with excel.

What I'm trying to achieve. Take all contents of a column, push the data into a bat file. Script will be launched from a button on another worksheet.

UPDATED Full Code: (Tried Ryan's answer, was getting error. Fixed it but then the script did nothing.)

Sub Send2Bat()

    Dim ColumnNum: ColumnNum = 26   ' Column Z - I have the I J and K Columns concatenated there.
    Dim RowNum: RowNum = 0
    Dim objFSO, objFile

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("C:\Test\Convert.bat")    'Output Path
    aFile = "C:\Test\Convert.bat"


    Dim OutputString: OutputString = ""

    Dim LastRow: LastRow = Application.ActiveSheet.Cells(Application.ActiveSheet.Rows.Count, ColumnNum).End(xlUp).Row

    Do
nextloop:
        RowNum = RowNum + 1
        If (IsEmpty(Cells(RowNum, ColumnNum).Value)) Then
            GoTo nextloop:
        End If
        OutputString = OutputString & Replace(Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine


    Loop Until RowNum = LastRow

    objFile.Write (OutputString)

    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

Upvotes: 0

Views: 52

Answers (1)

Ryan B.
Ryan B.

Reputation: 3665

I made an excel workbook and put some data in column z for Sheet 1 and Sheet 2.

enter image description here

And I tweaked your code to read as follows:

Sub Send2Bat()

Dim ColumnNum: ColumnNum = 26   ' Column Z - I have the I J and K Columns concatenated there.
Dim RowNum: RowNum = 0
Dim objFSO, objFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Test\Convert.bat")    'Output Path
aFile = "C:\Test\Convert.bat"

Dim OutputString: OutputString = ""
Dim targetSheet As Worksheet
Set targetSheet = Application.Worksheets("Sheet1")

Dim LastRow: LastRow = targetSheet.Cells(targetSheet.Rows.Count, ColumnNum).End(xlUp).Row

Do

    RowNum = RowNum + 1

    If Not (IsEmpty(targetSheet.Cells(RowNum, ColumnNum).Value)) Then

        OutputString = OutputString & Replace(targetSheet.Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine

    End If

Loop Until RowNum = LastRow

objFile.Write (OutputString)

Set objFile = Nothing
Set objFSO = Nothing

End Sub

And it produced the following file:

enter image description here

So then I updated the targetSheet name to "Sheet2"

Set targetSheet = Application.Worksheets("Sheet2")

and executed again. The file updated to this:

enter image description here

So, the code is good at least in its basic form. Do you have anything else updating the sheets or moving things around while this is happening?

Upvotes: 1

Related Questions