Excel Com Use... Finding it very confusing

[SpencerH]

I'm watching videos on YouTube and reading the tutorials here and in other places, but I'm just not getting it.

Right now if I was going to just use the GUI I would click or select cell A:3 of sheet 2 then I would press {ctrl down}{shift down}{end}{shift up}{ctrl up}. then I would copy that data to the end of sheet 1 then repeat for sheet 3.

Its pretty straightforward but I want to use the com cause it seems like it will be safer and more fault tolerant. Can someone help me out on this?

[Odlanir]

Code: Select all

xlApp := ComObjActive("Excel.Application")
loop, 3
    xlWS%a_index% := xlApp.Sheets(a_index)
xlApp.Visible :=true
xlApp.DisplayAlerts := false
XLDOWN := -4121

xlWS2.Range(xlWS2.Cells(3,1),xlWS2.Cells(1,1).End(XLDOWN)).copy
xlWS1.Cells(1,1).End(XLDOWN).offset(1,0).select
xlWS1.Paste
xlWS3.Range(xlWS3.Cells(3,1),xlWS3.Cells(1,1).End(XLDOWN)).copy
xlWS1.Cells(1,1).End(XLDOWN).offset(1,0).select
xlWS1.Paste
xlapp.CutCopymode := false
xlWS1.Cells(1,1).select
ExitApp

[SpencerH]

Using what you posted I'm trying to build something that will delete a column. This is as far as I have gotten so far and it is not working yet:

Code: Select all

#Warn
;xlApp := ComObjGet("C:\Users\mrhob\Desktop\AHK Testing\test.xlsx")
xlApp := ComObjActive("Excel.Application")

loop, 3
	xlWS%a_index% := xlApp.Sheets(a_index)
xlApp.Visible :=true
xlApp.DisplayAlerts := true

xlWS2.Range("A1").Delete("Shift:=xlToLeft")

;MsgBox, % DelCol
ExitApp

[Odlanir]

Easy:

Code: Select all

XLTOLEFT := -4159   
xlWS2.Columns(1).Delete(XLTOLEFT)

[SpencerH]

I have still been stuck on trying to get this to work. I have noteven been able to get the simple examples to work. Here is what steps I am trying to do as simply as I think I can explain them at this point. I'd say I'm not entirely sure what all the things should be called that I am doing.

1. Delete first workseet (quickbooks help)
2. Remove extra rows from 3 sheets
3. Remove extra column from 3 sheets
4. copy contents of sheet 2 and append to bottom of sheet 1
5. copy contents of sheet 3 and append to bottom of sheet 1
6. Delete sheets 2 & 3
7. remove and extra column on sheet 1 where some items are blank and others are not (we combine it such B+C then delete C)
8. save as CSV

I tried for a short time to do this in the CSV file using regex but I get errors in the output occasionally.

[FanaticGuru]

I have still been stuck on trying to get this to work. I have noteven been able to get the simple examples to work. Here is what steps I am trying to do as simply as I think I can explain them at this point. I'd say I'm not entirely sure what all the things should be called that I am doing.

1. Delete first workseet (quickbooks help)
2. Remove extra rows from 3 sheets
3. Remove extra column from 3 sheets
4. copy contents of sheet 2 and append to bottom of sheet 1
5. copy contents of sheet 3 and append to bottom of sheet 1
6. Delete sheets 2 & 3
7. remove and extra column on sheet 1 where some items are blank and others are not (we combine it such B+C then delete C)
8. save as CSV

I tried for a short time to do this in the CSV file using regex but I get errors in the output occasionally.

Well, lets start simple and grow from there.

Lets do some deleting. This will delete stuff in the active workbook, open in Exel.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.Sheets(1).Delete ; delete first sheet
xlApp.Sheets(2).Rows(2).Delete ; delete second row in second sheet
xlApp.Sheets(2).Columns(3).Delete ; delete third column in second sheet

If you get that to work, we can grow from there.

FG

[FanaticGuru]

Since this is talking about deleting... below is code I find very useful for deleting blank columns between columns containing data.

Code: Select all

xlApp := ComObjActive("Excel.Application")
col_left := xlApp.ActiveSheet.UsedRange.Column
col_right := xlApp.ActiveSheet.UsedRange.Columns.Count + col_left
while !(col_left > --col_right)
	if (xlApp.WorksheetFunction.CountA(xlApp.Columns(col_right)) = 0)
		xlApp.Columns(col_right).Delete

/* VBA CODE
'
' Columns_DeleteBlank   :   Deletes all empty columns on the active worksheet
'
Sub Columns_DeleteBlank()
Dim iCol As Integer
    With ActiveSheet.UsedRange
        For iCol = .Column + .Columns.Count - 1 To 1 Step -1
            If Application.WorksheetFunction.CountA(.Columns(iCol)) = 0 Then
                Columns(iCol).Delete
            End If
        Next iCol
    End With
End Sub
*/

QuickBooks creates alot of blank columns when it exports to Excel.

One trick of the code is that it has to go right to left. This will not delete blank cells to the left side that are not actually between columns of data. This is by design but could easily be changed.

I also included the VBA code that I use in Excel. It is very useful to put this code in you Personal Excel VBA library and then create a custom button on the ribbon to run this macro so it can be used from within Excel any time as if it was a native feature of Excel.

FG

[SpencerH]

@FanaticGuru Thank you!!!

based on your example I was able to take it to the next step and get rid of all the rows and columns that need to be gone.

Not that you need to see this but this is what I have so far

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xlApp := ComObjActive("Excel.Application")
xlApp.Sheets(1).Delete ; delete first sheet
xlApp.Sheets(1).Rows(1).Delete ; delete second row in second sheet
xlApp.Sheets(1).Rows(2).Delete

xlApp.Sheets(2).Rows(1).Delete
xlApp.Sheets(2).Rows(2).Delete

xlApp.Sheets(3).Rows(1).Delete
xlApp.Sheets(3).Rows(2).Delete


xlApp.Sheets(1).Columns(8).Delete ; delete third column in second sheet
xlApp.Sheets(1).Columns(6).Delete

xlApp.Sheets(2).Columns(8).Delete
xlApp.Sheets(2).Columns(6).Delete

xlApp.Sheets(3).Columns(8).Delete
xlApp.Sheets(3).Columns(6).Delete

I will work to see if I can get any further on my own but I am looking very much forward to your next reply!

PS just for fun here is what we replaced (3 times)

Code: Select all

send, ^{End}
	send, {Left}
	send, ^{Space}
	send, ^-
	ToolTip, The first column should be gone,
	sleep, 5000
	send, {Left}
	Send, {Left}
	sleep, 5000
	send, ^{Space}
	send, ^-
	ToolTip, The second column should be gone,
	sleep, 5000
	send, ^{Home}
	send, {Up}
	Send, {Shift Down}{Space}{Shift Up}
	send, ^-
	ToolTip, One of the rows should be gone,
	sleep, 5000
	send, {up}
	send, {Shift Down}{Space}{Shift Up}
	send, ^-
	ToolTip, The last row should be gone,
	sleep, 5000
	; Save name of Sheet this is just as per working co report this is CEIS here
	send, !h
	sleep, 3500
	send, o
	send, r
	sleep, 2500
	send, Towers
	ToolTip, Now the Sheet should be named,
	send, {Enter}
	sleep, 3000

This is the next part where we copy the data.

Code: Select all

WinActivate, Microsoft Excel - scrape.xlsx,
send, ^{PgDn 5}
sleep, 1000
send, ^{Home}
sleep, 500
;send, {Shift Up}
sleep, 1000
send, {Up 4}
sleep, 1000
send, {Left 10}
sleep, 1000
send, {Shift Down}^{End}{Shift Up}
sleep, 500
;send, {shift Up}
sleep, 1000
send, ^c
sleep, 1000
send, ^{PgUp}
sleep, 1000
send, ^{End}
sleep, 1000
send, {Up}
sleep, 1000
send, {Left 10}
sleep, 1000
send, ^v
sleep, 1000
send, ^{Home}
sleep, 500
;send, {Shift Up}
sleep, 1000
send, {Up 4}
sleep, 1000
send, {Left 10}
sleep, 1000
send, {Shift Down}^{End}{Shift Up}
sleep, 500
;send, {shift Up}
sleep, 1000
send, ^c
sleep, 1000
send, ^{PgUp}
sleep, 1000
send, ^{End}
sleep, 1000
send, {Up}
sleep, 1000
send, {Left 10}
sleep, 1000
send, ^v
sleep, 1000
ExitApp

[SpencerH]

Now I have figured out how to get row counts using this

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xlApp := ComObjActive("Excel.Application")
xlApp.DisplayAlerts := false

sheet1_row_count := xlApp.Sheets(1).UsedRange.Rows.Count

sheet2_row_count := xlApp.Sheets(2).UsedRange.Rows.Count

sheet3_row_count := xlApp.Sheets(3).UsedRange.Rows.Count

MsgBox, %sheet1_row_count% %sheet2_row_count% %sheet3_row_count%

My thought is to make a selection that says select from row 1 a thru g down to sheetx_row_count then copy then head over to sheet one and go to sheet1_row_count minus 1 and past in column A. If I get this figured out I will post back!

[FanaticGuru]

4. copy contents of sheet 2 and append to bottom of sheet 1
5. copy contents of sheet 3 and append to bottom of sheet 1

Next you probably don't really need to copy content from one cell to others. That would mean you need to copy formatting and everythring.

You probably only need to make the value of some cells equal to the vaule of other cells.

Here are some examples:

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.Sheets(2).Range("A1").Value := xlApp.Sheets(1).Range("A1").Value  ; one cell range
xlApp.Sheets(2).Range("B2:D4").Value := xlApp.Sheets(1).Range("B2:D4").Value  ; multi-cell range
xlApp.Sheets(2).Range("F7:G8").Value := xlApp.Sheets(1).Range("D5:E6").Value ; multi-cell range to different range but same size
xlApp.Sheets(2).Columns(10).Value := xlApp.Sheets(1).Columns(6).Value ; column range
xlApp.Sheets(2).Rows(9).Value := xlApp.Sheets(1).Rows(4).Value ; row range
xlApp.Sheets(2).Cells(10,6).Value := xlApp.Sheets(1).Cells(10,6).Value ; one cell using 'cells' which uses R1C1 notation
xlApp.Sheets(2).Cells(10,6).Resize(2,2).Value := xlApp.Sheets(1).Cells(10,6).Resize(2,2).Value ; using 'cells' which uses R1C1 notation but resizing area to 2x2 range

You can make any ranges values equal to any other range of the same size.

If you really need to transfer something other than "value" then that can be done also.

Now to transfer the values of all the cells used in one sheet to the first empty row of another sheet:

Code: Select all

xlApp := ComObjActive("Excel.Application")
Row_EmptyBelowData := xlApp.Sheets(1).UsedRange.Find("*",,,,,2).Offset(1,0).Row	; get row number just below last cell in sheet 1 with anything in it
xlRange_Source := xlApp.Sheets(2).UsedRange ; Source Range (UsedRange is a special range of all non-empty cells)
xlApp.Sheets(1).Cells(Row_EmptyBelowData, 1).Resize(xlRange_Source.Rows.Count, xlRange_Source.Columns.Count).Value := xlRange_Source.Value 

The main line at the end is mostly about getting the destination range the same size as the source range.

FG

[SpencerH]

You can ignore this post as I found several typos. (midday slump)

My new code is starting to look a little hairy and does some funny things when I add in the last move operation. Can you see what I am doing wrong here?

Code: Select all

Row_EmptyBelowData1 := xlApp.Sheets(1).UsedRange.Find("*",,,,,2).Offset(-1,0).Row
Row_EmptyBelowData2 := xlApp.Sheets(2).UsedRange.Find("*",,,,,2).Offset(1,0).Row
Row_EmptyBelowData3 := xlApp.Sheets(3).UsedRange.Find("*",,,,,2).Offset(1,0).Row
; xlApp.Sheets(2).Range(A1

mvValue1 := Row_EmptyBelowData1 + Row_EmptyBelowData2

BaseRange = A%Row_EmptyBelowData1%:G%mvValue1%
P2Range = A1:G%Row_EmptyBelowData2%
P3Range = A1:G%Row_EmptyBelowData2%
; xlApp.Sheets(2).Range("A1").Value := xlApp.Sheets(1).Range("A1").Value  ; one cell range
xlApp.Sheets(1).Range(BaseRange).Value := xlApp.Sheets(2).Range(P2Range).Value  ; multi-cell range


newREBD1 := := xlApp.Sheets(1).UsedRange.Find("*",,,,,2).Offset(1,0).Row

mvValue2 := newREBD1 + Row_EmptyBelowData3
	
newBaseRange = A%newREBD1%:G%mvValue2%
	
xlApp.Sheets(1).Range(newBaseRange).Value := xlApp.Sheets(2).Range(P3Range).Value  ; multi-cell range
	
MsgBox, %Row_EmptyBelowData1%
MsgBox, %BaseRange%
	

[SpencerH]

Everything was looking really good but on my development machine with Excel 2016, but when I tried to test to the point we are right now none of the "move" operations worked on the Excel 2007 version. So what do I need to change here?

Code: Select all

WinActivate, Microsoft Excel - scrape.xlsx,
xlApp := ComObjActive("Excel.Application")
xlApp.DisplayAlerts := false
xlApp.Sheets(1).Delete ; delete first sheet

; delete first sheet rows
xlApp.Sheets(1).Rows(1).Delete 
xlApp.Sheets(1).Rows(1).Delete

; delete second sheet rows
xlApp.Sheets(2).Rows(1).Delete
xlApp.Sheets(2).Rows(1).Delete

; delete third sheet rows
xlApp.Sheets(3).Rows(1).Delete
xlApp.Sheets(3).Rows(1).Delete

; delete first sheet columns
xlApp.Sheets(1).Columns(8).Delete ; delete third column in second sheet
xlApp.Sheets(1).Columns(6).Delete

; delete second sheet columns
xlApp.Sheets(2).Columns(8).Delete
xlApp.Sheets(2).Columns(6).Delete

; delete thrid sheet columns
xlApp.Sheets(3).Columns(8).Delete
xlApp.Sheets(3).Columns(6).Delete

Row_EmptyBelowData1 := xlApp.Sheets(1).UsedRange.Find("*",,,,,2).Offset(-1,0).Row
Row_EmptyBelowData2 := xlApp.Sheets(2).UsedRange.Find("*",,,,,2).Offset(1,0).Row
Row_EmptyBelowData3 := xlApp.Sheets(3).UsedRange.Find("*",,,,,2).Offset(1,0).Row
; xlApp.Sheets(2).Range(A1

mvValue1 := Row_EmptyBelowData1 + Row_EmptyBelowData2

BaseRange = A%Row_EmptyBelowData1%:G%mvValue1%
P2Range = A1:G%Row_EmptyBelowData2%
P3Range = A1:G%Row_EmptyBelowData3%
; xlApp.Sheets(2).Range("A1").Value := xlApp.Sheets(1).Range("A1").Value  ; one cell range
xlApp.Sheets(1).Range(BaseRange).Value := xlApp.Sheets(2).Range(P2Range).Value  ; multi-cell range NOT WORKING IN 2007

newREBD1 := xlApp.Sheets(1).UsedRange.Find("*",,,,,2).Offset(1,0).Row

mvValue2 := newREBD1 + Row_EmptyBelowData3

newBaseRange = A%newREBD1%:G%mvValue2%

xlApp.Sheets(1).Range(newBaseRange).Value := xlApp.Sheets(3).Range(P3Range).Value  ; multi-cell range NOT WORKING in 2007

;xlApp.Sheets(2).Delete
;xlApp.Sheets(2).Delete

extraRows := mvValue2 - 2
xlApp.Sheets(1).Rows(extraRows).Delete
xlApp.Sheets(1).Rows(extraRows).Delete
xlApp.Sheets(1).Rows(extraRows).Delete

extraRows := mvValue1 - 2
xlApp.Sheets(1).Rows(extraRows).Delete
xlApp.Sheets(1).Rows(extraRows).Delete
xlApp.Sheets(1).Rows(extraRows).Delete
;MsgBox, %extraRows%
MsgBox, %mvValue1%

[FanaticGuru]

Everything was looking really good but on my development machine with Excel 2016, but when I tried to test to the point we are right now none of the "move" operations worked on the Excel 2007 version. So what do I need to change here?

Code: Select all

mvValue1 := Row_EmptyBelowData1 + Row_EmptyBelowData2

BaseRange = A%Row_EmptyBelowData1%:G%mvValue1%
P2Range = A1:G%Row_EmptyBelowData2%

Your math is wrong in this code resulting in the source and destination range not being the same size.

Code: Select all

; if this was true
Row_EmptyBelowData1 := 1,  Row_EmptyBelowData2 := 2

mvValue1 := Row_EmptyBelowData1 + Row_EmptyBelowData2
; then 3 := 1 + 2

BaseRange = A%Row_EmptyBelowData1%:G%mvValue1%
P2Range = A1:G%Row_EmptyBelowData2%
; then
; BaseRange = A1:G3
; P2Range = A1:G2

There might be other problems but this alone is problem enough to cause problems.

FG