Split excel file in many small ones

[jeeswg]

Normally (e.g. on Excel 2007) you can just do:
- 'View, Macros, Record Macro...'
- do a few things manually to the Excel sheet(s)/workbook(s)
- 'View, Macros, View Macros'

90% of the time you can find the basis of the code you need, sometimes key things are not recorded.

In this case: Columns("A:A").EntireColumn.AutoFit

[WalkerOfTheDay]

Hi,

I adapted a small piece of the code.
I want to be able to select the file which needs to be split, so I used FileSelectFile

I want the files to be saved in the same directory as the un-split file, so I used SplitPath
But that needs a backslash added at the back. I solved it like this, but would there be a single
line command to store saveDir including a backslash ??

Code: Select all

FileSelectFile, wbkPath, 1, , Open het gewenste bestand, *.xlsx ;select the workbook to use.
SplitPath, % wbkPath ,, saveDir	; store the directory from the workbook location
SaveDir = %SaveDir%\			; add a backslash to SaveDir

[jeeswg]

Some ideas (if anyone has any other RegEx ideas or good links for RegEx and splitting paths, please share):

Code: Select all

;path to dir (but keep trailing backslash)
vPath := A_ScriptFullPath

SplitPath, vPath,, vDir
vDir .= "\" ;it does take one more line to append a backslash, but it's not a bad option

SplitPath, vPath,, vDir
MsgBox, % vDir "\"
MsgBox, % RegExReplace(vPath, "(?!.*\\).*$", "")
MsgBox, % RegExReplace(vPath, "[^\\]*$", "")
MsgBox, % RegExReplace(vPath, ".*\\\K.*", "")
MsgBox, % SubStr(vPath, 1, InStr(vPath, "\", 0, 0)) ;AHK v1
MsgBox, % SubStr(vPath, 1, InStr(vPath, "\", 0, -1)) ;AHK v2

[EDIT:] @IMEime, nice suggestion, cheers. I've added a similar RegEx line above, which keeps the trailing backslash.

[kon]

Code: Select all

FileSelectFile, wbkPath, 1, , Open het gewenste bestand, *.xlsx ;select the workbook to use.
SplitPath, % wbkPath ,, saveDir	; store the directory from the workbook location
SaveDir = %SaveDir%\			; add a backslash to SaveDir

I would do it just as you have done there. Maybe some small changes.

Here is a revised script.
All columns in the new workbooks are autofit.
This shows how to change the number format in certain columns. For now, it changes the number format in column C.
I added the slash to the SaveAs line.
Lines changed are marked with ; <--.

Code: Select all

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

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

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

; Make Excel visible.
;~ xlApp.Visible := true

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

; 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  ; <--
    
    ; Change the number format in column 'C' (the 3rd column).
    wbkNew.Worksheets(1).Columns(3).NumberFormat := "0.00"  ; <--

    ; 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.
wbkSrc.Close()

; 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
}

[IMEime]

Some ideas (if anyone has any other RegEx ideas or good links for RegEx and splitting paths, please share):...

humble one of mine

Code: Select all

MsgBox % RegExReplace( A_ScriptFullPath, ".*\K\\.*")

I do not want to be cofused with given/fixed/hidden function.
so i prefer to use RegEx, it is so clear..
and to not to lose my thin memory..


one has to learn the expression first...

[WalkerOfTheDay]

Thanks again kon. I have the Excel file at work, but maybe if I can find the time I'll remote in this weekend to test.

About the formatting to 2 decimals, will that piece of code also replace commas to points?

@the rest:
Sorry about feeding the troll. I won't do it again.
Usually it's not my style to behave like that.

[FanaticGuru]

Thanks FG !

I will test it later today. One remark though: (I didn't tell you this so you couldn't know) Not every section will contain the word "Aktiven" so I think
your solution would fail at this point right ?

It is actually simpler if a blank line is assumed to be the divider and start of a new file.

Code: Select all

FileSelectFile, File_Source, 1, % A_Desktop "\Test", Selecte Source File, *.xls?
SplitPath, File_Source,, Folder_Dest
xlApp := ComObjCreate("Excel.Application")
xlApp.Visible := false	; true to see results
xlApp.DisplayAlerts := false ; SaveAs will overwrite files
xlSheet := xlApp.Workbooks.Open(File_Source).Sheets(1)
Last := xlSheet.UsedRange.Rows.Count
Row := 1
while (Row < Last)
{
	Row ++
	xlWB := xlApp.Workbooks.Add
	xlSheet.Rows(1).EntireRow.Copy(xlWB.Worksheets(1).Range("A1"))
	RowTo := 2
	while (xlSheet.Cells(Row,1).Value != "")
	{
		xlSheet.Rows(Row).EntireRow.Copy(xlWB.Worksheets(1).Cells(RowTo, 1))
		Row ++, RowTo ++
	}
	xlWB.Worksheets(1).Columns.AutoFit
	xlWB.SaveAs(Folder_Dest "\" xlWB.ActiveSheet.Range("B2").Formula ".xlsx")
	xlWB.Close()  ; Comment out to see results
}
xlApp.Quit() ; Comment out to see results
MsgBox DONE

Also added selection of Excel source file and autofit of all columns.

FG

[kon]

About the formatting to 2 decimals, will that piece of code also replace commas to points?

Not sure, you will need to test, but I suspect it will. I get a decimal by default (I'm guessing that has to do with regional or language default settings).

Edit:
I recorded a macro to change the numberformat to go from decimal to comma. Here's the relevant part: .NumberFormat = "#,##0.00"
You might need to do something similar to go from comma to decimal.[Edit3] I was mistaken. It changes the thousands separator, which is not the goal.[/Edit3]

Edit2:
Another option https://stackoverflow.com/questions/281 ... 1#28107021

And this post seems to support my earlier guess that it is a regional setting. (But I imagine changing that setting is probably not desirable.): https://www.mrexcel.com/forum/excel-que ... post543527

Edit3: Made a correction to the first edit.

[WalkerOfTheDay]

Okay,

So I tested both scripts and in terms of speed kon's script kicks FantasticGuru's ass
01:08 against 03:44 on a 21818 line excel file.

The autofit works. Many thanks !
The formatting thing seems a bit problematic. It turns out the software that creates the main excel file
saves every number as text. So I have no clue how to get around that.

If you want I could send you guys the main excel file. Just pm me your email(s). I don't want to make the
file available for the entire internet

[jeeswg]

Btw I believe one of the parameters, when you open an Excel workbook using macros, allows you to specify the locale.

[EDIT:]
Workbooks.Open Method (Excel)
https://msdn.microsoft.com/VBA/Excel-VB ... thod-excel

Code: Select all

wbkSrc := xlApp.Workbooks.Open(wbkPath)
wbkSrc := xlApp.Workbooks.Open(wbkPath,,,,,,,,,,,,,Local:=-1)

;m := ComObjMissing() ;AHK v1
m := ComObject(0xA, 0x80020004) ;AHK v1/v2
wbkSrc := xlApp.Workbooks.Open(wbkPath,m,m,m,m,m,m,m,m,m,m,m,m,Local:=-1)

[kon]

Are there commas in the main workbook file? Or do the commas only appear in the new workbooks?

[WalkerOfTheDay]

Are there commas in the main workbook file? Or do the commas only appear in the new workbooks?

Yes, there are commas in the main workbook.

[FanaticGuru]

Okay,

So I tested both scripts and in terms of speed kon's script kicks FantasticGuru's ass
01:08 against 03:44 on a 21818 line excel file.

Nice. The main difference in our two scripts is that kon collects the rows of a file all in one range and then copies the multi-row range at once whereas I copy each row as I go.

My code is shorter and more compact through!

Originally I thought about using a FindAll method to find all of the blank cells in the UsedRange of column A to find all the breaks. Then doing some math to calculate all the ranges to copy. It would probably be similar to kon's in speed. I believe the main thing is doing as few individual copies as possible.

Maybe I will play around with eliminating the while (xlSheet.Cells(Row,1).Value != "") in my code to something smarter. Maybe a Ctrl+Down or FindNext to jump to the end of each block.

If you are going to have to do a bunch of comma/period fixing of individual cells that is really going to slow things up.

I would work on trying to fix that in the main sheet before starting.

FG

[FanaticGuru]

Are there commas in the main workbook file? Or do the commas only appear in the new workbooks?

Yes, there are commas in the main workbook.

So is it always two decimals like money? 567,98
Do they have periods for thousand separators? 1.567,98
Are they all in one column?

FG

[FanaticGuru]

The more I look at it the smaller the code gets.

Code: Select all

FileSelectFile, File_Source, 1, % A_Desktop "\Test", Selecte Source File, *.xls?
SplitPath, File_Source,, Folder_Dest
xlApp := ComObjCreate("Excel.Application")
xlSheet := xlApp.Workbooks.Open(File_Source).Sheets(1)
Last := xlSheet.UsedRange.Rows.Count, Start := 2
Loop
{
	if (xlSheet.Cells(Start+1,1).Formula = "")
		End := Start
	else
		End := xlSheet.Cells(Start, 1).end(-4121).Row
	xlRows := xlSheet.Range(Start ":" End).EntireRow
	xlWB := xlApp.Workbooks.Add
	xlSheet.Rows(1).EntireRow.Copy(xlWB.Worksheets(1).Range("A1"))
	xlRows.Copy(xlWB.Worksheets(1).Range("A2"))
	xlWB.Worksheets(1).Columns.AutoFit
	xlWB.SaveAs(Folder_Dest "\" xlWB.ActiveSheet.Range("B2").Formula ".xlsx")
	xlWB.Close()
	Start := End+2
} until (Start > Last)
xlApp.Quit()

Copy rows except for header all at once which should be faster.

FG

[WalkerOfTheDay]

Amazing stuff !
Now it's much faster then your previous code, and about 3 seconds faster then kon's.

Regarding the numbers. The main file contains comma's, and no thousands seperators.
But somehow they are stored as text, not as numbers.

When I look at the properties of a cell it's states 'standard', but when I click the exclamation sign next to it
it's giving the message 'number stored as text'. There is no ' sign infront of the number though.

The more I look at it the smaller the code gets.

Code: Select all

FileSelectFile, File_Source, 1, % A_Desktop "\Test", Selecte Source File, *.xls?
SplitPath, File_Source,, Folder_Dest
xlApp := ComObjCreate("Excel.Application")
xlSheet := xlApp.Workbooks.Open(File_Source).Sheets(1)
Last := xlSheet.UsedRange.Rows.Count, Start := 2
Loop
{
	if (xlSheet.Cells(Start+1,1).Formula = "")
		End := Start
	else
		End := xlSheet.Cells(Start, 1).end(-4121).Row
	xlRows := xlSheet.Range(Start ":" End).EntireRow
	xlWB := xlApp.Workbooks.Add
	xlSheet.Rows(1).EntireRow.Copy(xlWB.Worksheets(1).Range("A1"))
	xlRows.Copy(xlWB.Worksheets(1).Range("A2"))
	xlWB.Worksheets(1).Columns.AutoFit
	xlWB.SaveAs(Folder_Dest "\" xlWB.ActiveSheet.Range("B2").Formula ".xlsx")
	xlWB.Close()
	Start := End+2
} until (Start > Last)
xlApp.Quit()

Copy rows except for header all at once which should be faster.

FG

[kon]

it's giving the message 'number stored as text'.

That gives us something specific to search for:
https://duckduckgo.com/?q=excel+number+ ... ffsb&ia=qa

This might work for fixing the main workbook numbers.
https://stackoverflow.com/questions/185 ... 2#18553572
Once the main workbook has been opened, try to fix numbers stored as text:

Code: Select all

; Constants
xlCellTypeConstants := 2
xlPasteValues := -4163
xlPasteSpecialOperationMultiply := 4

;...

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

; Once the main workbook has been opened, try to fix numbers stored as text.

; Pick an unused cell on the second sheet.
rConst := wbkSrc.Worksheets(2).Cells(1, 4)
rConst.Value := 1

rng := wbkSrc.Worksheets(1).Cells.SpecialCells(xlCellTypeConstants)
rng.NumberFormat := "General"
rConst.Copy
rng.PasteSpecial(xlPasteValues, xlPasteSpecialOperationMultiply)

rConst.Clear

;...

/* Sub ConvertToNumber()
 *     Dim rng As Range
 *     Dim cl As Range
 *     Dim rConst As Range
 * 
 *     ' pick an unused cell
 *     Set rConst = Cells(1, 4)
 *     rConst = 1
 * 
 *     Set rng = Cells.SpecialCells(xlCellTypeConstants)
 *     rng.NumberFormat = "General"
 *     rConst.Copy
 *     rng.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
 * 
 *     rConst.Clear
 * End Sub
 */

I don't have Excel handy at the moment, so the above is untested.

[WalkerOfTheDay]

Thanks kon,

I'm trying to put this code in between both yours and FG's script, but I cannot
get it to work

It's giving all sorts of errors:

Spoiler

---------------------------
Excel splitter.ahk
---------------------------
Error: 0x8002000B - Ongeldige index.
Source: (null)
Description: (null)
HelpFile: (null)
HelpContext: 0

Specifically: Worksheets

Line#
024: FileSelectFile,wbkPath,1,%A_ScriptDir%,Open het gewenste bestand,Workbooks (*.xlsx; *.xls)
025: if (ErrorLevel)
026: Return
029: SplitPath,wbkPath,,saveDir
032: xlApp := ComObjCreate("Excel.Application")
035: xlApp.Visible := false
038: wbkSrc := xlApp.Workbooks.Open(wbkPath)
---> 041: rConst := wbkSrc.Worksheets(2).Cells(1, 4)
042: rConst.Value := 1
044: rng := wbkSrc.Worksheets(1).Cells.SpecialCells(xlCellTypeConstants)
045: rng.NumberFormat := "General"
046: rConst.Copy
047: rng.PasteSpecial(xlPasteValues, xlPasteSpecialOperationMultiply)
049: rConst.Clear
053: HeaderRow := wbkSrc.Worksheets(1).Rows(1)

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

Spoiler

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

Specifically: NumberFormat

Line#
029: SplitPath,wbkPath,,saveDir
032: xlApp := ComObjCreate("Excel.Application")
035: xlApp.Visible := false
038: wbkSrc := xlApp.Workbooks.Open(wbkPath)
041: rConst := wbkSrc.Worksheets(2).Cells(1, 4)
042: rConst.Value := 1
044: rng := wbkSrc.Worksheets(1).Cells.SpecialCells(xlCellTypeConstants)
---> 045: rng.NumberFormat := "General"
046: rConst.Copy
047: rng.PasteSpecial(xlPasteValues, xlPasteSpecialOperationMultiply)
049: rConst.Clear
053: HeaderRow := wbkSrc.Worksheets(1).Rows(1)
056: myCell := wbkSrc.Worksheets(1).Range("A2")
059: While,myCell.Formula != ""
059: {

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

Spoiler

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

Specifically: PasteSpecial

Line#
035: xlApp.Visible := false
038: wbkSrc := xlApp.Workbooks.Open(wbkPath)
041: rConst := wbkSrc.Worksheets(2).Cells(1, 4)
042: rConst.Value := 1
044: rng := wbkSrc.Worksheets(1).Cells.SpecialCells(xlCellTypeConstants)
045: rng.NumberFormat := "General"
046: rConst.Copy
---> 047: rng.PasteSpecial(xlPasteValues, xlPasteSpecialOperationMultiply)
049: rConst.Clear
053: HeaderRow := wbkSrc.Worksheets(1).Rows(1)
056: myCell := wbkSrc.Worksheets(1).Range("A2")
059: While,myCell.Formula != ""
059: {
062: rng := FindContinuousRange(myCell)
065: wbkNewName := myCell.Offset(0, 1).Text

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

it's giving the message 'number stored as text'.

That gives us something specific to search for:
https://duckduckgo.com/?q=excel+number+ ... ffsb&ia=qa

This might work for fixing the main workbook numbers.
https://stackoverflow.com/questions/185 ... 2#18553572
Once the main workbook has been opened, try to fix numbers stored as text:

Code: Select all

; Constants
xlCellTypeConstants := 2
xlPasteValues := -4163
xlPasteSpecialOperationMultiply := 4

;...

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

; Once the main workbook has been opened, try to fix numbers stored as text.

; Pick an unused cell on the second sheet.
rConst := wbkSrc.Worksheets(2).Cells(1, 4)
rConst.Value := 1

rng := wbkSrc.Worksheets(1).Cells.SpecialCells(xlCellTypeConstants)
rng.NumberFormat := "General"
rConst.Copy
rng.PasteSpecial(xlPasteValues, xlPasteSpecialOperationMultiply)

rConst.Clear

;...

/* Sub ConvertToNumber()
 *     Dim rng As Range
 *     Dim cl As Range
 *     Dim rConst As Range
 * 
 *     ' pick an unused cell
 *     Set rConst = Cells(1, 4)
 *     rConst = 1
 * 
 *     Set rng = Cells.SpecialCells(xlCellTypeConstants)
 *     rng.NumberFormat = "General"
 *     rConst.Copy
 *     rng.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
 * 
 *     rConst.Clear
 * End Sub
 */

I don't have Excel handy at the moment, so the above is untested.

[kon]

It's giving all sorts of errors:

That first error is probably because I tried to use a cell on the second sheet... but I'm guessing your workbook doesn't have a second sheet? That should be fixed.

This seemed to work when I briefly tested it.
It should change the "numbers stored as text" to numbers. Once it changes them to numbers, you should be able to set .NumberFormat for the columns you want. Right now it changes the number format in column 'C' so you will need to adjust that part for your needs.

Code: Select all

; Constants
xlCellTypeConstants := 2
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"

; 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
}

[WalkerOfTheDay]

You nailed it !

At first I did again go an error message but that has to do with
the language of excel I guess.

I had to change

this:

Code: Select all

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

To:

Code: Select all

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

Would there be a way to check the excel language before running the script ? 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.

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