Split excel file in many small ones

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Split excel file in many small ones

11 Jul 2017, 08:42

Try: .NumberFormat := ""
This post says that it will set the numberformat to general/standard. If that doesn't work we can try checking the value of .NumberFormatLocal.
WalkerOfTheDay wrote:Also, how would I translate this to AHK:

Code: Select all

Range("A1").Select
   Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
Untested:

Code: Select all

MyRange := wbkSrc.Worksheets(1).Range("A1")
MyRange := xlApp.Range(MyRange, MyRange.End(-4161))  ; xlToRight = -4161
MyRangeInt := MyRange.Interior
MyRangeInt.Pattern := 1  ; xlSolid = 1
MyRangeInt.PatternColorIndex := -4105  ; xlAutomatic = -4105
MyRangeInt.Color := 65535  ; xlYellow = 65535
MyRangeInt.TintAndShade := 0
MyRangeInt.PatternTintAndShade := 0
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

12 Jul 2017, 03:29

Yes the numberformat thing works !

The 'Untested' part doesn't.
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Split excel file in many small ones

12 Jul 2017, 10:35

Code: Select all

#IfWinActive ahk_class XLMAIN

^F12::  ; Ctrl+F12
    xlApp := Excel_Get()
    MyRange := xlApp.ActiveCell
    MyRange := xlApp.Range(MyRange, MyRange.End(-4161))  ; xlToRight = -4161
    MyRangeInt := MyRange.Interior
    MyRangeInt.Pattern := 1  ; xlSolid = 1
    MyRangeInt.PatternColorIndex := -4105  ; xlAutomatic = -4105
    MyRangeInt.Color := 65535  ; xlYellow = 65535
    MyRangeInt.TintAndShade := 0
    MyRangeInt.PatternTintAndShade := 0
    xlApp := "", MyRange := "", MyRangeInt := ""
return

#If

Esc::ExitApp

; Excel_Get - https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; <Paste the Excel_Get function definition here>
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

14 Jul 2017, 02:21

I noticed something odd,

Every time I start the script for the first time I get this error (after booting up my pc):
Spoiler
Regarding your code, I'm not quite sure what to do with it. I visisted the thread mentioned
and tried to run the code and got: Call to nonexistent function

If I run the excel_get.ahk code, and open excel and then press F7 it's only displaying the Caption and the Workbook.

I'm probably misunderstanding what I should do here ? Excuse my ignorance.
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Split excel file in many small ones

14 Jul 2017, 12:26

WalkerOfTheDay wrote:I noticed something odd,

Every time I start the script for the first time I get this error (after booting up my pc):
Spoiler
I don't know. When I google it, most advice seems to say that there is something wrong with your PC. It's weird that it only happens when you first boot up.

This search had no AHK/Excel-specific results.
WalkerOfTheDay wrote:The reason I ask is, at work I localy work with a Dutch
version of Excel 2007.
But on our cloudserver we have an English version of Excel 2016.

WalkerOfTheDay wrote:Regarding your code, I'm not quite sure what to do with it. I visisted the thread mentioned
and tried to run the code and got: Call to nonexistent function

If I run the excel_get.ahk code, and open excel and then press F7 it's only displaying the Caption and the Workbook.

I'm probably misunderstanding what I should do here ? Excuse my ignorance.
"Call to nonexistent function" means that you do not have the Excel_get function definition in your script.

Starting at the line:

Code: Select all

; Excel_Get by jethrow (modified)
[...]
... copy-and-paste this --> into --> this. Then press Ctrl+F12.



A basic example of a function is:

Code: Select all

^F12::
    MyFunction()  ; <-- CALLING a function.
return

F7::
    MyFunction()  ; <-- CALLING a function.
return



MyFunction() {  ; <-- function DEFINITION.
    MsgBox %A_ThisHotkey%
}
You need to copy-and-paste the Excel_Get function definition.
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

14 Jul 2017, 14:42

Thanks I'll give it a try.

Regarding the error, it's happening on these different pc's in our metwork. So it doesn't seem to be an isolated issue. The only thing similar on these three pc's is the OS which is Vista (I know bad) and Avast Antivirus.
Monday I'll try to run it from our cloud server (Windows Server 2016).

@Edit
Hmm. So I uploaded the script to our cloud server, and now the error does not occur !
So it seems to be an issue with Windows Vista.

But it's strange that I cannot find anyone else with the same issue using Vista on google.
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

19 Jul 2017, 04:00

I tried the Excel_Get function now. I now understand how it works. Very usefull thanks so much.
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Split excel file in many small ones

19 Jul 2017, 10:44

WalkerOfTheDay wrote:I tried the Excel_Get function now. I now understand how it works. Very usefull thanks so much.
I changed the forum and github pages so that (hopefully) it is a bit less confusing for other people in the future.
WalkerOfTheDay wrote:The only thing similar on these three pc's is the OS which is Vista (I know bad) and Avast Antivirus.
[...]
Hmm. So I uploaded the script to our cloud server, and now the error does not occur !
So it seems to be an issue with Windows Vista.

But it's strange that I cannot find anyone else with the same issue using Vista on google.
Yes, strange that there are so few search results. Just guessing: maybe it's the Avast AV? Maybe a language or regional setting?
When I google '0x8007007E' the cause of the error seems to be: "You tried instantiate an object that is not registered on the local system." But that makes it even weirder that it only happens when you first boot up. :crazy:
But, even if we don't know the exact cause of the 0x8007007E error, we could do some workarounds. You could use try / catch to ignore that particular error and then have your script try again. Or another alternative could be to use Run and WinWait to launch Excel and then use Excel_Get to get the application object:

Code: Select all

; Potential workaround for 0x8007007E error.
; Instead of 'xlApp := ComObjCreate("Excel.Application")'
Run, Excel,,, ExcelPID  ; Run Excel
WinWait, % "ahk_class XLMAIN ahk_pid " ExcelPID  ; Wait for the window to appear.
xlApp := Excel_Get("ahk_class XLMAIN ahk_pid " ExcelPID)  ; Get an Excel application object.
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

20 Jul 2017, 07:39

Now this is very weird...

I tried your suggested Try / Catch option and now it works without throwing an exception ! :think:

You would think the MsgBox would fire, but it doesn't. Either way I'm happy :)

Code: Select all

Try
{
	xlApp := ComObjCreate("Excel.Application")	
}
catch e 
{
	MsgBox, An exception was thrown!`Specifically: %e%`nTrying again !
	xlApp := ComObjCreate("Excel.Application")	
}
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

20 Jul 2017, 09:05

Darn !!

For some reason on the Cloud Server (excel 2016), the dates get formatted wrong.

30.06.2017 get's stored as 42916 :shock:

I tried this, but it's giving strange output: 6/d/yyyy

Code: Select all

wbkSrc.Worksheets(1).Columns(1).NumberFormat := "m/d/yyyy"	; column A
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Split excel file in many small ones

20 Jul 2017, 17:52

It took me a while but I think I have a potential solution. I set up a test workbook with some dates formatted as text and the following code seems to work.
(Your system has different date/time regional settings; I suspect that may complicate things. But for now give this a try.)
The lines I've changes are marked with ; <--- Added.

Code: Select all

; Constants
xlCellTypeConstants := 2
xlDelimited := 1  ; <--- Added
xlDMYFormat := 4  ; <--- Added
xlPasteValues := -4163
xlPasteSpecialOperationMultiply := 4

; Select the workbook to use.
FileSelectFile, wbkPath, 1, %A_ScriptDir%, Open, Workbooks (*.xlsx; *.xls)
if (ErrorLevel)  ; User pressed cancel.
    return

; Store the workbook directory.
SplitPath, % wbkPath,, saveDir

; Create an instance of Excel
xlApp := ComObjCreate("Excel.Application")

; Make Excel visible. This line can be removed once you check that this script is working properly.
xlApp.Visible := true

; Change decimal separator to comma.
xlApp.DecimalSeparator := ","
xlApp.ThousandsSeparator := " "
xlApp.UseSystemSeparators := false

; Open the source workbook.
wbkSrc := xlApp.Workbooks.Open(wbkPath)

; Pick an unused cell. (The last cell in column A.)
rConst := wbkSrc.Worksheets(1).Columns(1).Cells( wbkSrc.Worksheets(1).Columns(1).Cells.Count )

; Set the cell value to 1.
rConst.Value := 1

; Get a range of all cells of type: Constants.
rng := wbkSrc.Worksheets(1).Cells.SpecialCells(xlCellTypeConstants)

; Change number format to "General".
rng.NumberFormat := "General"

; Copy.
rConst.Copy

; Paste special.
rng.PasteSpecial(xlPasteValues, xlPasteSpecialOperationMultiply)

; Clear the cell that was set earlier.
rConst.Clear

; Change decimal separator to dot.
xlApp.DecimalSeparator := "."
xlApp.ThousandsSeparator := ","

; ***I'm not sure what columns need to be changed in your workbook.***
; This changes the number format in column 'C' (the 3rd column).
wbkSrc.Worksheets(1).Columns(3).NumberFormat := "0.00"

; Convert dates stored as text to dates. (Column A)
SA := ComObjArray(0xC, 2), SA[0] := 1, SA[1] := xlDMYFormat  ; <--- Added
wbkSrc.Worksheets(1).Columns(1).Cells.TextToColumns(wbkSrc.Worksheets(1).Range("A1"), xlDelimited,,,,,,,,, SA)  ; <--- Added
wbkSrc.Worksheets(1).Columns(1).NumberFormat := "m/d/yyyy"  ; <--- Added

; Get the header row from sheet1.
HeaderRow := wbkSrc.Worksheets(1).Rows(1)

; Get cell A2 on sheet1.
myCell := wbkSrc.Worksheets(1).Range("A2")

; Loop until 'myCell' is blank.
while myCell.Formula != "" {
    
    ; Get a continuous range of cells based on which cells in column A are not blank.
    rng := FindContinuousRange(myCell)
    
    ; Get the name to use for the new workbook.
    wbkNewName := myCell.Offset(0, 1).Text
    
    ; Add a new blank workbook.
    wbkNew := xlApp.Workbooks.Add()
    
    ; Copy the header into the new workbook.
    HeaderRow.Copy( wbkNew.Worksheets(1).Range("A1") )
    
    ; Copy 'rng' into the new workbook.
    rng.Copy( wbkNew.Worksheets(1).Range("A2") )
    
    ; Autosize all columns on sheet1 in the new workbook.
    wbkNew.Worksheets(1).Columns.AutoFit
    
    ; SaveAs
    wbkNew.SaveAs(saveDir "\" wbkNewName ".xlsx")
    
    ; Close the new workbook.
    ;~ MsgBox  ; Pause to see the workbook.
    wbkNew.Close()
    
    ; Get the cell to use for the next loop. (down 2 rows in column A)
    rng := rng.Columns(1)
    myCell := rng.Cells(rng.Cells.Count).Offset(2, 0)
}

; Close the source workbook. 0 = Do not save changes.
wbkSrc.Close(0)

xlApp.UseSystemSeparators := true

; Quit Excel.
xlApp.Quit()
return

FindContinuousRange(rCell) {
    ; Reference http://sitestory.dk/excel_vba/find-next-empty-cell.htm
    static xlDown := -4121
    
    ; If the cell just below is blank.
    if (rCell.Offset(1, 0).Formula = "")
        return rCell.EntireRow
    ; Finds the last cell with content.
    ; .End(xlDown) is like pressing CTRL + down.
    else
        return rCell.Application.Range(rCell, rCell.End(xlDown)).EntireRow
}
References and further reading: When I was searching for answers, many people suggested using DateValue(). But that is a VBA function (not part of Excel, its part of the VBA programming language) so we can't use if from AHK.
User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Split excel file in many small ones

20 Jul 2017, 18:05

Just thought I'd draw attention to this again, to open a workbook and specify local settings:
Split excel file in many small ones - Page 2 - AutoHotkey Community
https://autohotkey.com/boards/viewtopic ... 41#p158141
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

21 Jul 2017, 09:15

Thanks again for the effort Kon.
Unfortunatly it only seems to format the month.

42851 (26.04.2017), becomes 4/d/yyyy.

@jeeswg. Thanks I'll have a read. Will take some time though, about to go on vacaction :)

Edit:

I think I'm on to something the Locale (location) of this excel version is set to German (Germany)

So this:

wbkSrc.Worksheets(1).Columns(1).NumberFormat := "m/d/yyyy" ;(Month / Day / Year)

Must be converted to this:

wbkSrc.Worksheets(1).Columns(1).NumberFormat := "m/t/jjjj" ;(Monat / Tag / Jahr)

To bad this is language specific :?
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Split excel file in many small ones

21 Jul 2017, 10:29

I would look into jeeswg's suggestion more.

And also maybe,

Code: Select all

if (A_Language = "0407")  ; languageCode_0407 = German_Standard  https://www.autohotkey.com/docs/misc/Languages.htm
    NF := "m/t/jjjj"  ; (Monat / Tag / Jahr)
else if (A_Language = "0409")  ; languageCode_0409 = English_United_States
    NF := "m/d/yyyy"  ; (Month / Day / Year)
else {
    MsgBox, 48, Language, Language not supported (%A_Language%)
    return
}

wbkSrc.Worksheets(1).Columns(1).NumberFormat := NF
Edit:
To bad this is language specific :?
If everyone just used ISO 8601 it would be so much easier. :D
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

01 Sep 2017, 08:11

Hi Kon,

It's been a while, (vacation) but I have a little question how do I incorporate this piece
of code for Column B and C as well ?

Code: Select all

; Convert dates stored as text to dates. (Column A) ; <--- Added
SA := ComObjArray(0xC, 2), SA[0] := 1, SA[1] := xlDMYFormat  ; <--- Added
wbkSrc.Worksheets(1).Columns(1).Cells.TextToColumns(wbkSrc.Worksheets(1).Range("A1"), xlDelimited,,,,,,,,, SA)  ; <--- Added
wbkSrc.Worksheets(1).Columns(1).NumberFormat := "d/m/jjjj"  ; <--- Added
I tried the above code and change the .Columns(1) part to .Columns(2) and .Columns(3)
but that doesn't seem to work at all.
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

29 Nov 2017, 04:04

Friendly bump. Seems like Kon hasn't been in this forum for a long time. Could some have a look a the question above this one please ?

I really don't undersand all this COM stuff (yet).
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Split excel file in many small ones

29 Nov 2017, 13:37

WalkerOfTheDay wrote:Hi Kon,

It's been a while, (vacation) but I have a little question how do I incorporate this piece
of code for Column B and C as well ?

Code: Select all

; Convert dates stored as text to dates. (Column A) ; <--- Added
SA := ComObjArray(0xC, 2), SA[0] := 1, SA[1] := xlDMYFormat  ; <--- Added
wbkSrc.Worksheets(1).Columns(1).Cells.TextToColumns(wbkSrc.Worksheets(1).Range("A1"), xlDelimited,,,,,,,,, SA)  ; <--- Added
wbkSrc.Worksheets(1).Columns(1).NumberFormat := "d/m/jjjj"  ; <--- Added
I tried the above code and change the .Columns(1) part to .Columns(2) and .Columns(3)
but that doesn't seem to work at all.
I know very little about region stuff but I thought German was:
wbkSrc.Worksheets(1).Columns(1).NumberFormat := "m/t/jjjj" ;(Monat / Tag / Jahr)
not "d/m/jjjj"

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

29 Nov 2017, 13:48

Thanks for your input FG, much appreciated.

However, my question was specifically about the piece of code I quoted above. Column A now gets converted properly to a date. But column B and C need the same conversion.
I cannot seem to get it working.
User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Split excel file in many small ones

29 Nov 2017, 14:48

WalkerOfTheDay wrote:Thanks for your input FG, much appreciated.

However, my question was specifically about the piece of code I quoted above. Column A now gets converted properly to a date. But column B and C need the same conversion.
I cannot seem to get it working.
You have to change both Columns reference and the Range("A1") to have it operate on a different column.

Something like this:

Code: Select all

wbkSrc.Worksheets(1).Columns(2).Cells.TextToColumns(wbkSrc.Worksheets(1).Range("B1"), xlDelimited,,,,,,,,, SA)  ; <--- Added
wbkSrc.Worksheets(1).Columns(2).NumberFormat := "d/m/jjjj"  ; <--- Added
FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
User avatar
WalkerOfTheDay
Posts: 710
Joined: 24 Mar 2016, 03:01

Re: Split excel file in many small ones

30 Nov 2017, 09:31

Thanks again FG.

When I add that I get this error:

Code: Select all

---------------------------
Excel splitter gui.ahk
---------------------------
Error:  0x800A03EC - 
Source:		Microsoft Office Excel
Description:	Eigenschap SaveAs van klasse Workbook kan niet worden opgehaald.
HelpFile:		C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM
HelpContext:	0

Specifically: SaveAs

	Line#
	188: Sleep,1
	189: }
	190: GuiControl,,MyProgress
	195: wbkNew := xlApp.Workbooks.Add()
	198: HeaderRow.Copy( wbkNew.Worksheets(1).Range("A1") )  
	201: rng.Copy( wbkNew.Worksheets(1).Range("A2") )  
	204: wbkNew.Worksheets(1).Columns.AutoFit  
--->	208: wbkNew.SaveAs(saveDirTest "\" wbkNewName ".xlsx")  
	212: wbkNew.Close()  
	215: rng := rng.Columns(1)
	216: myCell := rng.Cells(rng.Cells.Count).Offset(2, 0)
	217: }
	219: Gui,Font,cGreen
	220: GuiControl,Font,AccNumber
	221: GuiControl,,AccNumber,Done !

Continue running the script?
---------------------------
Ja   Nee   
---------------------------

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: Google [Bot], JoeWinograd, yabab33299 and 142 guests