norfaezahsuhaimi

How To Access An .xlsb File By Php?

2023-10-07T11:55:00.001-07:00

I need to access an .xlsb file that does the same function as this code that accesses a .csv file: Access the .xlsb file, scroll through the columns and rows, and display the row

Solution 1:

You can use the PHP Excel library from EasyXLS.

$xls = new COM("EasyXLS.ExcelDocument");
$rows = $xls->easy_ReadXLSBActiveSheet_AsList("Trabalhos.xlsb");
for ($row1=0; $row1<$rows->size(); $row1++)
{
    $rowLine = $rows->elementAt($row1);
        if ($row1 == 2) {
            $column1[6]."<br>";
            $valor1 = $row->elementAt(6);
        }
}

You can find some more code samples about importing data from xlsb file.

Solution 2:

For your purpose you can use PHPOffice/PHPExcel

Sql Query To Pull Records Of Exception Entry

2023-10-07T09:36:00.001-07:00

I am new to Excel VBA. I have a UserForm in which I am trying to populate records of exception entered by employee for the current month. I have a database called Ofc. Under which

Solution 1:

A couple of points:

I'd suggest you parameterise your query. You only need one daft so-and-so to rename his ID "0; DROP TABLE Codeusage" or some such hilarious prank and unsanitised SQL text commands can give you a whole world of hurt.
Column names with spaces are ripe for causing trouble and are usually considered poor design, so I'd remove those spaces if you possibly can. If not then you'll need to quote your column names. You'll have to check what works for your particular system but straight brackets would be a good first bet.

You'll have to adjust the code below to meet the specific requirements of your database (you don't mention which one), but this is what I would do for something like SQL Server or SQLite. Note, I've referenced Microsoft ActiveX Data Objects 6.1 Library via Tools -> References):

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim firstOfMonth AsDateDim lastOfMonth AsDateDim PeoplesoftId AsString'Open the database connectionSet conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open "[Your connection string goes here]"'Define your query parameters
PeoplesoftId = "43243312"
firstOfMonth = DateSerial(Year(Now), Month(Now), 1)
lastOfMonth = DateSerial(Year(firstOfMonth), Month(firstOfMonth) + 1, 0)

'Create your commandSet cmd = New ADODB.Command
cmd.ActiveConnection = conn

'Command text
cmd.CommandText = "SELECT * FROM CodeUsage" & _
                  " WHERE PeoplesoftId=?" & _
                  " AND Date_of_exception>=?" & _
                  " AND Date_of_exception<=?"'note I've replaced your spaces with underscore

cmd.CommandType = adCmdText

'Command parametersSet prm = cmd.CreateParameter(, adChar, adParamInput, Len(PeoplesoftId), PeoplesoftId)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter(, adDate, adParamInput, , firstOfMonth)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter(, adDate, adParamInput, , lastOfMonth)
cmd.Parameters.Append prm

'Execute the command - returns a RecordSetSet rs = cmd.Execute

'Close the connection
conn.Close

How To Check If Two Ranges Value Is Equal

2023-10-07T08:29:00.001-07:00

I want to merge cells in columns, if there is the same value in whole row. Eg. If A1:G1 range is the same as A2:G2 I want to merge A1:A2 cells, B1:B2 to G1:G2. With my code below

Solution 1:

The issue is that …

Range(Cells(i, 7), Cells(i, 24)).Value

returns an array of values, but you cannot compare an array of values with =. Therefore you need to loop throug all these values and compare each value with the corresponding value in

Range(Cells(i - 1, 7), Cells(i - 1, 24)).Value

Since you already have this loop just move your If statement to check this into the loop:

Dim iRow AsLong, iCol AsLong, LastRow asLong
LastRow = Cells(Rows.Count, 6).End(xlUp).row
For iRow = LastRow To7Step -1For iCol = 7To24Step1If Cells(iRow, iCol).Value = Cells(iRow - 1, iCol).Value Then
            Range(Cells(iRow, iCol), Cells(iRow - 1, iCol)).Merge
        EndIfNext iCol 
Next iRow

Note that I changed the variable naming to more meaningful names. This also avoids using Row as variable name which is alerady used by Excel itself.

Edit according comments

OptionExplicitSub Test()
    Dim RangeToMerge As Range
    Set RangeToMerge = Range("C5:F14")

    Dim FirstMergeRow AsLong
    FirstMergeRow = 1Dim iRow AsLong, iCol AsLongFor iRow = 1To RangeToMerge.Rows.Count - 1IfJoin(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(RangeToMerge.Rows(FirstMergeRow).Value)), "|") <> _
           Join(Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(RangeToMerge.Rows(iRow + 1).Value)), "|") ThenIf iRow <> FirstMergeRow ThenFor iCol = 1To RangeToMerge.Columns.Count
                    Application.DisplayAlerts = False
                    RangeToMerge.Cells(FirstMergeRow, iCol).Resize(rowsize:=iRow - FirstMergeRow + 1).Merge
                    Application.DisplayAlerts = TrueNext iCol
            EndIf
            FirstMergeRow = iRow + 1EndIfNext iRow

    'merge last onesIf iRow <> FirstMergeRow ThenFor iCol = 1To RangeToMerge.Columns.Count
            Application.DisplayAlerts = False
            RangeToMerge.Cells(FirstMergeRow, iCol).Resize(rowsize:=iRow - FirstMergeRow + 1).Merge
            Application.DisplayAlerts = TrueNext iCol
    EndIfEndSub

Will turn the following

into

Solution 2:

The value property of a range returns an array if the range has more than one cell. You can either compare the values of each element in a loop, or you can use join() to convert the arrays to strings and then compare those (see this answer).

Copy Data Between Excel Spreadsheets With A Given Value As A Parameter

2023-10-07T07:54:00.001-07:00

Say I have two spreadsheets. One of the spreadsheets has a table like this: **Sheet 1** A B C D 1 Name Age Rank Number 2 George 22 Ma

Solution 1:

vlookup would be the easiest way to go. I will use cell B2 in sheet2 as the reference to try to explain the function =VLOOKUP(A2,Sheet1!$A$2:$D$5,2,FALSE) The first parameter is the data you are using for a lookup - you may want to change that to $A2 if you copy the formula around the sheet The second parameter is where the data is that excel needs to find the value you referenced in the first parameter - use dollar signs. The data doesn't tend to move The third parameter tells excel what info to return - the 2 means give me the 2nd piece of data The last parameter tells excel if it can use a close match (FALSE for an exact match)

Now to follow Excel and see what it returns.....

First, it looks for George in the data (it knows it has to be an exact match from the fourth parameter) Next, it finds the row George -- 22 -- Master -- 12 and gives the function the second value (22 in this case)

Check Is Destination Directory Exist Then Proceed If Not Then Create It And Proceed Afterwards

2023-10-07T06:50:00.001-07:00

I have a button on one of the worksheets that lets user to continue with his task to save his/her template as a separate workbook in the folder. Here is my code Private Sub Continu

Solution 1:

I am going to modularize your code a little bit:

First get the directory path here

FunctiongetDirectoryPath()
    getDirectoryPath = ThisWorkbook.Path & Application.PathSeparator & Settings.Range("C45").ValueEndFunction

You can create the directory using this function

Sub createDirectory(directoryPath)
    MkDir directoryPath
EndSub

You can check if a directory exists or not using Dir function

Dir(directoryPath, vbDirectory) 'empty string means directoryPath doesn't exist

The final function on button click:

PrivateSub ContinueButton_Click()
    Application.ScreenUpdating = 0
    Sheets(cmbSheet.Value).Visible = True
    directoryPath = getDirectoryPath
    'Creating the directory only if it doesn't existIf Dir(directoryPath, vbDirectory) = ""Then
         createDirectory directoryPath
    EndIf
    Application.Goto Sheets(cmbSheet.Value).[a22], True
    Application.ScreenUpdating = 1
    Unload MeEndSub

Solution 2:

I created a macro that will save as pdf certain tabs of my excel in a relative (variable)folder. It will use the contract reference to create a subfolder, such subfolder label is exactly the contract reference. If subfolder already exists it just creates the files in it, else (subfolder does not exist) then it creates the folder and save the files in it.

Sub Gera_PDF_MG_Nao_Produtor_Sem_Ajuste()

    Gera_PDF_MG_Nao_Produtor_Sem_Ajuste Macro



    Dim MyFolder AsStringDim LaudoName AsStringDim NF1Name AsString

    MyFolder = ThisWorkbook.path & "\" & Sheets("Laudo").Range("C9")
    LaudoName = Sheets("Laudo").Range("K27")
    NF1Name = Sheets("MG sem crédito e sem ajuste").Range("Q3")

    Sheets("Laudo").Select
    Columns("D:P").Select
    Selection.EntireColumn.Hidden = TrueIf Dir(MyFolder, vbDirectory) <> ""Then
    Sheets("Laudo").ExportAsFixedFormat Type:=xlTypePDF, filename:=MyFolder & "\" & LaudoName & ".pdf", Quality:=xlQualityMinimum, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False

    Sheets("MG sem crédito e sem ajuste").ExportAsFixedFormat Type:=xlTypePDF, filename:=MyFolder & "\" & NF1Name & ".pdf", Quality:=xlQualityMinimum, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    FalseElse
    MkDir MyFolder
    Sheets("Laudo").ExportAsFixedFormat Type:=xlTypePDF, filename:=MyFolder & "\" & LaudoName & ".pdf", Quality:=xlQualityMinimum, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False

    Sheets("MG sem crédito e sem ajuste").ExportAsFixedFormat Type:=xlTypePDF, filename:=MyFolder & "\" & NF1Name & ".pdf", Quality:=xlQualityMinimum, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    FalseEndIf

    Sheets("Laudo").Select
    Columns("C:Q").Select
    Selection.EntireColumn.Hidden = False
    Range("A1").Select'EndSub

Solution 3:

If Dir(Fldrpath, vbDirectory)= "" Then
MkDir Fldrpath
End If

Fldrpath refer to the Folderpath if Folder not found MkDir creates the folder

Excel Js Add-in Manifest Requirements Section

2023-10-07T03:36:00.001-07:00

we have excel js add with custom function support and because of it, this add-in works just in excel office 365 version. here we met a problem - 'npm validate' define this add-in a

Solution 1:

Custom Functions use separate requirement sets from the core Excel JavaScript APIs. You could refer to this page for Custom Functions requirement sets: Custom Functions requirement sets

Merge Excel Files Into One

2023-10-07T00:35:00.001-07:00

I have a few hundred Excel files, where each file has some data on the first sheet. I've been asked to write a console application in C# which merges all of the Excel files togethe

Solution 1:

You'll need to use the Microsoft Excel Interop library. http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx

You should be able to add a reference, in the com tab, to Microsoft Excel 11 Object Library.

http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx

Solution 2:

This is an easy task for a neat little program called Bulk File Merger (they have a Mac version called Excel File Merger). I managed to merge over 300 files in a matter of minutes. You can also merge csv files, which takes even less time. We banged out over 1300 csv files in seconds. Scary.

Solution 3:

It's not free (a developer license is $ 425) but GemBox has a very good library that is way faster than using the office objects. We use it in my current project since a year and a half, and it has consistently performed very well for us.

Solution 4:

Look into Visual Studio Tools for Office (VSTO). You can open workbooks in code and use a lot of the excel functionality, as well as copying worksheets like you've said you need to do.

For resources, this MSDN forum post demonstrates opening excel files and copying worksheets. Hopefully this will get you on the right direction.

Solution 5:

Of course you can do Tool->Compare->Merge Workbooks. And within Excel as Joshua mentioned there is the merge method in the COM Interop libraries. If you need some help let me know I work heavily in Excel and may be able to help you out on a small fee basis.

Excel-vba; Copy Data From Excel And Paste It In To A Word Template At Different Places

2023-10-06T22:04:00.001-07:00

I have some data in an Excel Sheet which have been pull from the master data in another sheet by the VBA. The data is showing in excel sheet in column A & B. And I have an MS-W

Solution 1:

Assuming that your data is in cells B1 to B7 of "Sheet1", then the following code will paste the required text into the document "C:\TestFolder\Test.docx". You can obviously edit the filenames and pathnames to suit.

Sub PasteStuffIntoWord()
Dim ws As Worksheet
Dim TextToPaste As String
Dim objWord As Object
Dim objSelection As Object

Set ws = Worksheets("Sheet1")
With ws
    TextToPaste = "An estimate amounting to Rs. " & .Range("B1").Value & _
    " based on approved rates for the above " & .Range("B2").Value & _
    " was sanctioned by the competent authority. Against this estimate tenders amounting to Rs. " _
    & .Range("B3").Value & " were invited vide Tender No. " & .Range("B4").Value & " Dated " _
    & .Range("B5").Value & " which has opened on " & .Range("B7").Value & " This estimate for Rs. " _
    & .Range("B1").Value & " has been approved on " & .Range("B5").Value
End With

    Set objWord = CreateObject("word.Application")
    objWord.documents.Open "C:\TestFolder\Test.docx"
    objWord.Visible = True
    Set objSelection = objWord.Selection
    objSelection.TypeText TextToPaste

End Sub

Copying A Variable Based Range Of Cells To Another Range

2023-10-06T21:41:00.001-07:00

Hello from my code it should be evident what I am trying to do at this point. I am attempting to copy a range of cells from a static portion of a worksheet to a created column but

Solution 1:

You should use .Value or .Value2 to transfer data between to ranges like this :

Ath.Range("D2: D" & LastAth).Value2 = Ath.Range(Ath.Cells(2, x), Ath.Cells(LastAth, x)).Value2

The main difference between these two is :

.Value2 gives you the underlying value of the cell (unformatted data)
.Value gives you the formatted value of the cell

For more details, take a look at Charles William's blog here.

As you seem to be working on two sheets (not on the "mapping" one in the code you gave, if I got that right. If not just change Ath. to Map. where it need to be), don't forget to use the references you created (I added them everywhere, even before Rows.Count and Columns.Count to avoid errors if you open an old document on a new Excel version)

I got rid of the Selects and shorten code where I could, but I let the "Mapping" sheet as I guessed you'll use it later in your code.

Also don't forget to free your abject variables like this, when you won't use it afterwards :

Set Ath = NothingSet Map = Nothing

Here is your code corrected, cleaned and tested :

Sub Mapping()

Dim Map As Worksheet, _
    Ath As Worksheet, _
    LastAth AsLong, _
    LastMap AsLong, _
    CLastAth AsLong, _
    x AsIntegerSet Ath = Sheets("Athena Greek God")
Set Map = Sheets("Mapping")
LastMap = Map.Cells(Map.Rows.Count, "D").End(xlUp).Row
LastAth = Ath.Cells(Ath.Rows.Count, "A").End(xlUp).Row

Ath.Columns("A:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Ath.Range("A1:D1").Value = Array("EDITED", "EDITED 2", "EDITED 3", "EDITED 4")
Ath.Columns("A:D").AutoFit

With Ath.Range("A1:D" & LastAth).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.149998474074526
    .PatternTintAndShade = 0EndWith


CLastAth = Ath.Cells(1, Ath.Columns.Count).End(xlToLeft).Column

For x = CLastAth To1Step -1If Ath.Cells(1, x) <> "The Principals Book"ThenElse
        Ath.Range("D2: D" & LastAth).Value = Ath.Range(Ath.Cells(2, x), Ath.Cells(LastAth, x)).Value
    EndIfNext x

Set Ath = NothingSet Map = NothingEndSub

Solution 2:

Take out the space after the :

I have also chopped your code down, Dimmed X and removed the selects for you:

Sub Mapping()

Dim Map As Worksheet, Ath As Worksheet, lastmap AsLong, lastath AsLong, X AsLong, Clastath AsLongSet Ath = Sheets("Athena Greek God")
Set Map = Sheets("Mapping")
lastmap = Map.Cells(Rows.Count, "D").End(xlUp).Row
lastath = Ath.Cells(Rows.Count, "A").End(xlUp).Row

Columns("A:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:D1") = Array("EDITED", "EDITED 2", "EDITED 3", "EDITED 4")
Columns("A:D").AutoFit
With Range("A1:D" & lastath).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.149998474074526
    .PatternTintAndShade = 0EndWith

Clastath = Ath.Cells(1, Columns.Count).End(xlToLeft).Column

For X = Clastath To1Step -1If Cells(1, X) = "The Principals Book"Then
        Range("D2:D" & lastath) = Range(Cells(2, X), Cells(lastath, X))
    EndIfNextEndSub

Edit: Also dimmed Clastath as long

Solution 3:

Actually, it is not really evident what this code should achieve, tell you why: Two worksheets are defined but only one of them is used, also It's not clear to which worksheet the code is to be applied. As it stand now, the code is applied to whatever worksheet is active.

See the code below with adjustments and comments. The code assumes the procedure should be apply to the Ath worksheet (change as needed)

Although the changes are explained, do let me know of any questions you might have about.

Option Explicit
Option Base 1

Sub Mapping()
Rem Worksheet "Map" isonly used to obtain lastmap which is never used
Rem Therefore theese line are commented as they do not play any role in the procedure'Dim Map As Worksheet
'Dim lastmap As Long
'Set Map = Sheets("Mapping")
'lastmap = Map.Cells(Rows.Count, "D").End(xlUp).Row' NOT USED?

Rem Set array with titles - easy to maintain, and use to command all further intructions avoiding hard codding
Dim aTitles As Variant
aTitles = [{"EDITED","EDITED 2","EDITED 3","EDITED 4"}]

Dim Ath As Worksheet
Dim lastath As Long
Dim Clastath As Integer
Dim X As Integer

    Set Ath = Sheets("Athena Greek God")

    Rem It's not clear to which worksheet the code isto be applied?
    Rem Actually it is applied to whatever worksheet is active
    Rem This code assumes the procedure should be apply to the Ath worksheet
    With Ath '(change as needed)
        lastath = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(1).Resize(, UBound(aTitles)).EntireColumn.Insert     'Using Titles arraytoinsert required number of columns
        WithRange(.Cells(1, 1), .Cells(lastath, UBound(aTitles)))  'Working with the range to be updated
            .Rows(1).Value = aTitles
            .Columns.AutoFit
            .Interior.Color = RGB(217, 217, 217)                    'Simplify methodtoset color

            Clastath = .Cells(1, Columns.Count).End(xlToLeft).Column

            Rem Use "Step -1" if you have more than one cell withvalue= "The Principals Book"
            Rem and you whant to catch the last occurrence. Otherwise no need to use it.
            Rem For X = Clastath To1 Step -1'(change if needed as per comment above)
            For X = 1 To Clastath
                If .Cells(1, X).Value = "The Principals Book" Then
                    Rem Old line, left only to show changes (.Value and .Value2)
                    Rem Ath.Range("D2: D" & lastath).Value = Ath.Range(Ath.Cells(2, X), Ath.Cells(lastath, X)).Value2
                    Ath.Range("D2: D" & lastath).Value = Ath.Range(Ath.Cells(2, X), Ath.Cells(lastath, X)).Value2
                    .Columns(4).Value = .Columns(1).Offset(0, X - 1).Value2
                    Exit For    'Exit For...Next after achieving its goal

    End If: Next: EndWith: EndWith

    Ath.Activate 'Only used to show\move to the worksheet updated

End Sub

Solution 4:

Your code deserves several comments. To begin with, you have to solve your problem (see point 1). In addition, several points can reduce the chances of error upon modification, and improve efficiency.

Use other methods for copying Ranges. You have to specify what you want to copy (data, formulas, number formats, etc.) to decide which method to use.

Copy only data.

Ath.Range("D2:D" & lastath).Value2 = Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Value2

Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Copy
Ath.Range("D2:D" & lastath).PasteSpecial xlPasteValues

Copy (part or all of) number formats. See this.

Ath.Range("D2:D" & lastath).Value = Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Value

Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Copy
Ath.Range("D2:D" & lastath).PasteSpecial xlPasteValuesAndNumberFormats

Copy formulas.

Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Copy
Ath.Range("D2:D" & lastath).PasteSpecial xlPasteFormulas  ' or xlPasteFormulasAndNumberFormats

Copy all.

Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Copy
Ath.Range("D2:D" & lastath).PasteSpecial xlPasteAll

Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Copy Destination:=Ath.Range("D2:D" & lastath)

Fully qualify your Ranges. This issue shows up once and again (e.g., this). What does this mean? Do not use Cells, Range, Rows or Columns without specifying which Worksheet they belong to, unless you specifically want to do that (and even in that case, explicitly using ActiveSheet improves readability and reduces the chances of errors, similar to using Option Explicit). For instance,
```
lastath = Ath.Cells(Rows.Count, "A").End(xlUp).Row
```
will take Rows.Count from the ActiveSheet, which might not be Ath. You likely do not want that. The correct form is
```
lastath = Ath.Cells(Ath.Rows.Count, "A").End(xlUp).Row
```
Fix all other code. Note: in this case, code continues execution and the mistake may go unnoticed, as it produces a valid result. In other cases, code without fully qualified Ranges will throw an error (e.g., with something like sheet1.Range(Cells(..., when sheet1 is not the ActiveSheet).

Your code seems inefficient. You might be copying data many times into the same Range. It is better to find the leftmost cell in row 1, containing "The Principals Book", and copy the range for that column into Range("D2:D" & lastath). Use

Dim x AsLongFor x = 1To Clastath
    If Ath.Cells(1, x) = "The Principals Book"Then
        Ath.Range("D2:D" & lastath).Value2 = Ath.Range(Ath.Cells(2, x), Ath.Cells(lastath, x)).Value2   ' or alternatives aboveExitForEndIfNext

It is not clear in which Worksheet you want columns inserted. It seems to be Ath. The other Worksheet is not used.

You can insert many columns at once. You can also enter data into a range at once.

Ath.Columns("A:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Ath.Range("A1:D1").Value = Array("EDITED", "EDITED 2", "EDITED 3", "EDITED 4")

Solution 5:

1.Remove the space in your string address: Before:

ath.Range("D2: D" & lastath))

After:

ath.Range("D2:D" & lastath))

2a. If you only want to copy the values, then use .value at the end of your range references: Before:

ath.Range("D2:D" & lastath).value = ath.Range(ath.Cells(2, x), ath.Cells(lastath, x))

After:

ath.Range("D2:D" & lastath).value = ath.Range(ath.Cells(2, x).value, ath.Cells(lastath, x).value).value

2b. If you want values and formats then use .copy (destination): Before:

ath.Range("D2:D" & lastath).value = ath.Range(ath.Cells(2, x), ath.Cells(lastath, x))

After:

ath.Range("D2:D" & lastath).copy(ath.Range(ath.Cells(2, x).value, ath.Cells(lastath, x).value))

Also, you should always reference the worksheet of a references range (e.g. ws.range("A1").value). You might also consider using a worksheet's .codename rather then .name if this isn't just a quick an dirty project.

How To Copy Specific Range Cells From Multiple Sheets To One Sheet?

2023-10-06T19:58:00.001-07:00

I have a master workbook in one folder and 100+ child workbooks in a different folder. Every week I need to copy a specific range of cells from the child workbooks (sheet name is s

Solution 1:

I fully agree with guys who told you that none will write the code for you, but you are lucky that I have that already written and there may be other people looking for same info, so here it is. Put the code to module in your master workbook and replace some data as commented:

OptionExplicitSub GoThroughFilesAndCopyData()

Dim BrowseFolder AsStringDim FileItem AsObjectDim oFolder AsObjectDim FSO AsObjectDim shtWork As Worksheet
Dim lngRow AsLongDim i AsLong: i = 1Dim strPath AsStringDim MasterSheet As Worksheet
Dim ChildSheet As Worksheet

Application.ScreenUpdating = False' selecting the folder to look files inWith Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder with child workbooks"
        .Show
        OnErrorResumeNext
        Err.Clear
        BrowseFolder = .SelectedItems(1)
        If Err.Number <> 0Then
            MsgBox "You didn't select anything!"
            Application.ScreenUpdating = TrueExitSubEndIfOnErrorGoTo0EndWithSet FSO = CreateObject("Scripting.FileSystemObject") ' creating filesystem objectSet oFolder = FSO.getfolder(BrowseFolder) ' creating folder objectSet MasterSheet = ThisWorkbook.Sheets("masterworksheet_name") 'replace masterworksheet_name with the name of your worksheet in master workbookForEach FileItem In oFolder.Files 'looking through each fileIf UCase(FileItem.Name) Like"*.XLS*"Then'try open only excel files

            i = MasterSheet.Cells(Rows.Count, 1).End(xlUp).row + 1' find last not empty row and get a next one which is empty
            Workbooks.Open (BrowseFolder & Application.PathSeparator & FileItem.Name)
            Set ChildSheet = Workbooks(FileItem.Name).Sheets("worksheet_name") 'worksheet_name - replace with child sheet nameWith ChildSheet ' replace your_range_to_copy with the range on a child sheet you want to copy
                Range("your_range_to_copy").Copy Destination:=MasterSheet.Cells(i, column_number) 'i - is the number of last empty row, replace column_number - must be the column number of range to insert
                .Parent.Close SaveChanges:=False'close child workbook without savingEndWithEndIfNext

    Application.ScreenUpdating = TrueEndSub

Solution 2:

This will do what you want.

Copy a range of each sheet

Note: This example use the function LastRow

This example copy the range A1:G1 fromeach worksheet.

Change the range in this code line

'Fill in the range that you want to copy'Set CopyRng = sh.Range("A1:G1")Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last AsLongDim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = FalseEndWith'Delete the sheet "RDBMergeSheet" if it exist
    Application.DisplayAlerts = FalseOnErrorResumeNext
    ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
    OnErrorGoTo0
    Application.DisplayAlerts = True'Add a worksheet with the name "RDBMergeSheet"Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"'loop through all worksheets and copy the data to the DestShForEach sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then'Find the last row with data on the DestSh
            Last = LastRow(DestSh)

            'Fill in the range that you want to copySet CopyRng = sh.Range("A1:G1")

            'Test if there enough rows in the DestSh to copy all the dataIf Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                MsgBox "There are not enough rows in the Destsh"GoTo ExitTheSub
            EndIf'This example copies values/formats, if you only want to copy the'values or want to copy everything look at the example below this macro
            CopyRng.Copy
            With DestSh.Cells(Last + 1, "A")
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = FalseEndWith'Optional: This will copy the sheet name in the H column
            DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

        EndIfNextExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = TrueEndWithEndSub

Also . . .

'Common Functions required for all routinesFunction LastRow(sh As Worksheet)
    OnErrorResumeNext
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    OnErrorGoTo0EndFunctionFunction LastCol(sh As Worksheet)
    OnErrorResumeNext
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    OnErrorGoTo0EndFunction

Invalid Sql Statement Expected; ‘delete’,’insert’,

2023-10-06T17:48:00.001-07:00

I have a code Dim Cn As New ADODB.Connection Dim cmd As ADODB.Command Set cmd = New ADODB.Command Dim i As Long Cn.ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;' & _

Solution 1:

The syntax for your parametered query doesn't look right.

DECLARE@TS_HD DOUBLE;
SET@TS_HD = ?TS_HD;
SELECT 
     So_HD
    ,Ngay_HD
    ,Ten_Khach_Hang
    ,Ma_So_Thue
    ,SUM(Doanh_So_KT) AS DoanhSo
    ,SUM(Thue_VAT) AS VAT 
FROM [BR$]
WHERE Ngay_HD <>NULLAND TS_HD =@TS_HD 
GROUPBY 
     So_HD
    ,Ma_So_Thue
    ,Ngay_HD
    ,Ten_Khach_Hang
ORDERBY 
     Ngay_HD

I'm not sure the provider supports DECLARE statements. If it doesn't, that's why you're getting this message. Let's try to remove it:

SELECT 
     So_HD
    ,Ngay_HD
    ,Ten_Khach_Hang
    ,Ma_So_Thue
    ,SUM(Doanh_So_KT) AS DoanhSo
    ,SUM(Thue_VAT) AS VAT 
FROM [BR$]
WHERE Ngay_HD <> NULL AND TS_HD = ?
GROUPBY 
     So_HD
    ,Ma_So_Thue
    ,Ngay_HD
    ,Ten_Khach_Hang
ORDERBY 
     Ngay_HD

Notice the WHERE clause, WHERE Ngay_HD <> NULL AND TS_HD = ? - the parameter placeholder is just a question mark.

I'm pretty sure that statement would work.

I'm less sure about how the parameter is passed in though:

SetTS_HD= cmd.CreateParameter("?TS_HD", adDouble, adParamInput)

I think you can drop the name parameter, it's optional - and some providers don't support named parameters. That sucks though, because now you need to name the arguments since the Name parameter is first in the signature of CreateParameter:

Set TS_HD = cmd.CreateParameter(Type:=adDouble, Direction:=adParamInput)

An alternative could be to New it up and initialize it "manually":

Dim param AsNew ADODB.Parameter
With param
    .Type = adDouble
    .Direction = adParamInput
    .Value = 0.01'in excel this value is 10% '?? really? it's 1% here!EndWith

cmd.Parameters.Append param

Change Link In Workbook Programmatically

2023-10-06T16:16:00.001-07:00

All, I have the following code to change a workbook's link. ThisWorkbook.ChangeLink 'OldPath.xlsx', 'NewPath.xlsx', xlLinkTypeExcelLinks My problem is that 'NewPath.xlsx' represent

Solution 1:

The only idea I have is a extended version of @d-stroyer solution. You need to replace part of the formulas including path to the document as well (or simply workbook reference if one is open):

Cells.Replace What:="C:\Users\Name\Desktop\[OldPath.xlsx]OldSheetName", _
    Replacement:="C:\Users\Name\Desktop\[NewPath.xlsx]NewSheetName", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Unfortunately, this will not work (I think, not tested) if you want to replace links in other excel elements like: series of charts, possibly data source of pivottables, etc.

Solution 2:

Yes, there is. Use a simple Replace statement :

Sub UpdateSheetName()

    Cells.Replace What:="OldSheetName", Replacement:="NewSheetName", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

If your workbook has other occurrences of the sheet name, and you don't want them to be caught by the search, use a limited range. Also, you can restrict by searching "OldSheetName!" (with the "!") and replacing by "NewSheetName!".

Select Column In A Selected Range Vba

2023-10-06T15:52:00.001-07:00

I'm trying to filter my table and copy just a column in my filtered range at a time. I tried to get column 2 in my range but it returns only the first cell. I also tried .entireCol

Solution 1:

Your selectedRange is a multiple area selection - in this instance, the columns property returns columns from only the first area of the range.

Instead, use the intersection of your selectedRange and the entirecolumn your are interested in - for instance with

Application.Intersect(selectedRange.columns(2).Entirecolumn,selectedRange)

Reading Cell Value With Applied Number Format Openpyxl? - Redux

2023-10-06T13:49:00.001-07:00

Python 2.7, openpyxl 2.4.x I'm trying to read a cell that is a date value ('05/01/2016') in excel and has custom number formatting applied ('yyyymm') so it displays as '201605'. Wh

Solution 1:

It's not implemented. What you are looking for is cell.number_format. Try the following:

defpyDateFormat(nf):
  import re
  """Convert xlsx cell.number_format to Python date Format."""#xlsx Format   = 'YYYYMMDD', 'YYYY\\-MM\\-DD'#Python Format = '%Y%m%d'
  nf = re.sub(r'\\',r'',nf,0,re.IGNORECASE) #Remove Escapes
  nf = re.sub(r'([^Y]*)(Y+)(.*)',r'\1%Y\3',nf,0,re.IGNORECASE) #Change YYYY to %Y
  nf = re.sub(r'([^M]*)(M+)(.*)',r'\1%m\3',nf,0,re.IGNORECASE) #Change MM to %m
  nf = re.sub(r'([^D]*)(D+)(.*)',r'\1%d\3',nf,0,re.IGNORECASE) #Change DD to %dreturn nf
#end def pyDateFormat

At the point you need the formated cell.value do:

  cell = wb.ws['A1']
  if cell.is_date:
    value = cell.value.date().__format__( pyDateFormat(cell.number_format) )

Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 4.3.3.2

Recursion Code In Vba

2023-10-06T12:53:00.001-07:00

I am trying to run this code to calculate Q(n) at different Tn in the Equation 16.4 in the attached picture.But its not giving me the correct output. I would appreciate any help.

Solution 1:

To solve a recursive function you can write it this way, for example

Function Factorial(n aslong) aslongIf n = 1Then
        Factorial = 1Else
        Factorial = n * Factorial(n-1)
    EndIfEndfunction

Yes, you can see For...Loop can also do the Factorial calculation, but in your case, its much easier to use recursive solution.

Besides Eq 16.4 is intentionally written as a recursive function. It is not written as a summation function because it is harder to do so. If given to you is a summation function, then you can apply the For...Loop solution.

Hope this helps.

EDIT

Function Q(n aslong) asdoubleIf n = 1Then
        Q = 5Else
        Q = Z * ( L * Q_t - (L + d * S) * Q(n-1) ) / ( Z * ( L + d * S ) )
    EndIfEndFunction

Notice that the function Q keep calling itself in Q(n-1) when n>1. That is called recursive solution.

(Check the formula. I might copy it wrong)

Updating Value Of The Target Cell In Excel Vba

2023-10-06T11:58:00.001-07:00

I am trying to update the value of the Target cell using VBA macro when it exists in particular Range. I want to update its value as by concatenating a string to its value. For ex

Solution 1:

Disable events prior to making any change that will fire the Change event:

Dim oldCellAddress AsStringDim oldCellValue AsStringPrivateSub Worksheet_Change(ByVal Target As Range)
    oldCellValue = 0IfNot Intersect(Target, Range("E10:E500")) IsNothingThen
        Application.EnableEvents = False
        oldCellValue = Target.Value
        Target.Value = "AST-" & oldCellValue
        Application.EnableEvents = TrueEndIfEndSub

If events aren't disabled, your change to the cell will fire the Worksheet_Change event, which will change the cell, which will fire the Worksheet_Change event, which will change the cell, which will fire the Worksheet_Change event, which will change the cell, which will fire the Worksheet_Change event, which will change the cell, which will fire the ...

Assuming you don't need your oldCellValue and oldCellAddress module-scope variables, but do want to handle changes to multiple cells, use this instead:

PrivateSub Worksheet_Change(ByVal Target As Range)
    IfNot Intersect(Target, Range("E10:E500")) IsNothingThenDim c As Range
        Application.EnableEvents = FalseForEach c In Intersect(Target, Range("E10:E500")).Cells
            c.Value = "AST-" & c.Value
        Next
        Application.EnableEvents = TrueEndIfEndSub

How To Wait For Xlwings To Complete Python Code In Vba?

2023-10-06T11:26:00.001-07:00

I am calling RunPython in VBA from the xlwings module with the following code: Sub Portfolio_Optimze() RunPython ('import quotes; quotes.get_quote()') SolverAdd CellRef:='$H$18:$H$

Solution 1:

Some info to start with: Excel on Mac only allows external processes to write to the cells when Excel is idle, i.e. while no Macros are running. That's why the RunPython call on Mac runs as background process that only really kicks in when the calling macro is finished running.

I would suggest one of the two following solutions:

1) Program the solver part in Python, too, for example using scipy.optimize.

2) Put the solver VBA into its own Sub and call it from Python. This is currently a planned feature for the next version of xlwings, see here, but you can work around for now like so (assuming wb is your Workbook):

app = xlwings.Application(wkb=wb)
app.xl_app.run_VB_macro('SolverMacro')

UPDATE, since v0.7.1 this is now properly supported:

>>>solver_macro = wb.macro('SolverMacro')>>>solver_macro()

Solution 2:

Another approach is to change a value in a hidden sheet at the end of your python code and with a Worksheet_Change snippet which will fire off some vba code. If hidden sheet is "Supplement" then add something like this in end of python code:

Range('Supplement', 'A1').value = Range('Supplement', 'A1').value +1

And the vba in the "Supplement" sheet could be:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1")) Is Nothing Then Worksheets("Main1").Range("A1").WrapText = True End If End Sub

Remember that Application.EnableEvents needs to be = True

Solution 3:

A bit clunky, maybe, but also look at Application.OnTime method which lets you schedule code to run at a set time. So put your solver code into another proc, call RunPython and then schedule the solver proc for, say, 10 seconds time.

Or, possibly a better answer, get the python code to either write to a Range or text file when done. The proc with solver code will look at the range or the file to see if python is done. If python not finished then schedules itself to run again in N secs, otherwise runs the solver.

Excel Vba: Can We Refer A Column By Its Name?

2023-10-06T10:25:00.001-07:00

I want to refer a column by its header name. Currently column is 4th one and header name is 'Preference'. And the column consists of 'Yes' or 'No' 5th column header is 'Reason' A

Solution 1:

Further to my comments, if you want to do it direct, you would have to do this:

cells(i,Application.WorksheetFunction.Match("Preference", Range("1:1"), 0)).

Solution 2:

Here is a function to find the column for X instance. I have put a subroutine in there to call it as an example for you.

Sub ColInstanceExample()
Dim MyColInstance AsLong
MyColInstance = ColInstance("Preference", 2) 'Pass in what you are searching for and the instance you want to return the column number forIf MyColInstance = 0Then
    MsgBox "Not Found"Else
    MsgBox "Found at column: " & MyColInstance
EndIfEndSubFunction ColInstance(HeadingString AsString, InstanceNum AsLong)
Dim ColNum AsLongOnErrorResumeNext
ColNum = 0For X = 1To InstanceNum
    ColNum = (Range("A1").Offset(0, ColNum).column) + Application.WorksheetFunction.Match(HeadingString, Range("A1").Offset(0, ColNum + 1).Resize(1, Columns.Count - (ColNum + 1)), 0)
Next
ColInstance = ColNum
EndFunction

Wait For Csv File To Open?

2023-10-06T09:21:00.001-07:00

I am trying to download and open a CSV file using VBA in Excel. When I step through using the debugger my code works fine but when I try to run it normally it won't work, it is try

Solution 1:

Usually such behavior happens not because of the code logic but because of interrupts. Depending on the nature of your environment it could be one of two things

some background refreshing process is getting executed sometime in between your code execution time causing some issues. e.g. in a financial services environment, your Bloomberg API could be doing its timely BlpUpdate event
your code blocks up a large chunk of time and causes unexpected behavior because event which was triggered during your loop was blocked too long waiting for your code to complete on the main thread.

So you need to try the following two solutions, one of which should work

For the second problem, inside your

DoWhile Condition
' ... your code ...Loop

add a line of code that checks for and clears any pending Event queues, making it...

DoWhile Condition
DoEvents     ' either put it at the start or at the end of your code' ... your existing code ...Loop

This will give the system some 'breathing space' to wrap up pending events. You'll need to experiment with the location of your DoEvents code.

For the first problem, you can try enclosing your entire loop within an Event Protected area of code, to ensure it finishes running before anything disturbs it.

Application.EnableEvents = False'your loop and other code comes here

Application.EnableEvents = True

Once again, you might need a bit of trial and error to see how much of the code needs to be thrown into the Event-free zone before it works as expected.

Calculating Slopes Of Fluorescence Over Time

2023-10-06T08:04:00.001-07:00

I am a newbie to R and want to use it to make my life easier analyzing the data of my fluorescence assays. Before I did the analysis manually in excel but now want to make it easi

Solution 1:

here is a possible solution

apply(df[,2:ncol(df)], 2, function(x){
  model = lm(x ~ df$time - 1)
  return(coef(model))
})
#ouput 
sample1 sample2 sample3 
4       3       2

It assumes the intercept is 0. If you would like the intercept evaluated use:

model = lm(x ~ df$time)

What this code does is to apply a function over columns (indicated by 2) of the data frame (df). It takes all columns from the second till the last one ([2:ncol(df]), and does a linear regression (model = lm(x ~ df$time - 1) and returns the coefficient(s) (return(coef(model)).

If the first column is not always named time then:

model = lm(x ~ df[,1] - 1)

to indicate the first column is the x

EDIT: problem is the variables were not coded as numeric. Here is a solution:

df$Time = as.numeric(df$Time) #convert time to numeric

Without intercept:

  apply(df[,2:ncol(df)], 2, function(x){
      x = as.numeric(x) #convert x to numeric
      model = lm(x ~ df$Time - 1)
      return(coef(model))
    })

With intercept:

apply(df[,2:ncol(df)], 2, function(x){
  x = as.numeric(x) #convert x to numeric
  model = lm(x ~ df$time)
  return(coef(model)[2])
})

Excel Vba Populate Listbox1 Based On Listbox2 Selection

2023-10-06T03:23:00.001-07:00

I am trying to figure out the proper code for populating a listbox based on a selection from a second listbox. I will explain my question the best I can. I have one worksheet with

Solution 1:

In the change event of the part number combobox do something like this.

Dim ws As Excel.Worksheet
Dim lRow AsLongSet ws = Worksheets("Sheet2")
lRow = 1'Loop through the rowsDoWhile lRow <= ws.UsedRange.Rows.count
    'Check if Column A has the value of the selected part number.If ws.Range("A" & lRow).Value = LstPartNum.TextThen
        UserForm1.LstLocation.Clear
        'Load the locations
        UserForm1.LstLocation.List = Split(ws.Range("B" & lRow).Value, ",")
        ExitDoEndIf
lRow = lRow + 1Loop

If your UserForm1.LstPartNum.List = Split() does not work to load the list, here is code to loop the split array.

Dim szLocs() AsStringDim i asinteger

szLocs= Split(ws.Range("B" & lRow).Value, ",")

i = 0'Loop though each tokenDoWhile i <= UBound(szPieces)
    UserForm1.LstPartNum.Additem szPieces(i)
i = i + 1Loop

Library To Write An Excel File

2023-10-06T00:40:00.001-07:00

I am using Microsoft Excel Interop library for exporting Excel file. However, the limit of this library is that: Require the user's computer must have Microsoft Excel. When write

Solution 1:

I have used EPPlus for .xlsx reading/writing and it has worked great

Solution 2:

I think the only way to achieve this without having Excel installed is to just write the file format directly. Microsoft documented the file format a few years ago. You can find them at http://msdn.microsoft.com/en-us/library/cc313118.aspx.

Update I've just found EPPlus. Check it out at http://epplus.codeplex.com/

Solution 3:

You can use an ODBC or OLE connector to read and write an Excel file. However, I am not sure if you can create a file with it, you might need to keep a dummy-empty file with the correct layout.

Update: It is, accordingly to comment below from @Joel Gauvreau possible to create an excel file using this technique.

Links: http://support.microsoft.com/kb/257819

http://www.codeguru.com/cpp/data/mfc_database/microsoftexcel/article.php/c1131

Vba - Loop Through Files In A Folder Only If Multiple Conditions Are Met

2023-10-05T23:54:00.001-07:00

I am currently using a piece of code to loop through files in a folder and copy certain cells from each file into a master list. There are a number of files being added into the fo

Solution 1:

I ran into a similar problem when copying a range into an array. What fixed it was using .Value2 instead of .Value. Might be worth a try.

How To Tell If Text Fits In A Cell?

2023-10-05T23:29:00.001-07:00

I would like to write some vba code that monitors the OnChange event for a sheet and does some adjustment if text does not fit a cell. I.e. make the text smaller or wrap etc.. I k

Solution 1:

Quick and dirty way which will not require you to check each and every cell.

I use this method to usually show all the data.

Sub Sample()
    With Thisworbook.Sheets("Sheet1").Cells
        .ColumnWidth = 254.86'<~~ Max Width
        .RowHeight = 409.5'<~~ Max Height
        .EntireRow.AutoFit
        .EntireColumn.AutoFit
    EndWithEndSub

I use this method if I want to wrap the text (If Applicable) and keep the row width constant

Sub Sample()
    With Thisworbook.Sheets("Sheet1").Cells
        .ColumnWidth = 41.71'<~~ Keep the column width constant
        .RowHeight = 409.5
        .EntireRow.AutoFit
    EndWithEndSub

Note: This is not applicable for merged cells. For that there is a separate method.

Solution 2:

I'm using THE "dirty" method - that's only one I know: force AutoFit and check new width/height.

However, we can't grantee that was chosen cell that forced new fit. So I opt by copying cell content to an empty worksheet.

That, of course, cause a lot of other problems, and more workarounds.

PrivateSub Worksheet_Change(ByVal Target As Range)
    IfNot Fits(Target) Then'Notice that Target may have multiple cells!!!EndIfEndSubFunction Fits(ByVal Range As Range) AsBooleanDim cell As Range, tmp_cell As Range, da AsBoolean, su AsBoolean'Stores current state and disables ScreenUpdating and DisplayAlerts
    su = Application.ScreenUpdating: Application.ScreenUpdating = False
    da = Application.DisplayAlerts: Application.DisplayAlerts = False'Creates a new worksheet and uses first cell as temporary cellSet tmp_cell = Range.Worksheet.Parent.Worksheets.Add.Cells(1, 1)
    'Assume fits by default
    Fits = True'Enumerate all cells in RangeForEach cell In Range.Cells
        'Copy cell to temporary cell
        cell.Copy tmp_cell
        'Copy cell value to temporary cell, if formula was usedIf cell.HasFormula Then tmp_cell.Value = cell.Value
        'Checking depends on WrapTextIf cell.WrapText Then'Ensure temporary cell column is equal to original
            tmp_cell.ColumnWidth = cell.ColumnWidth
            tmp_cell.EntireRow.AutoFit 'Force fittingIf tmp_cell.RowHeight > cell.RowHeight Then'Cell doesn't fit!
                Fits = FalseExitFor'Exit For loop (at least one cell doesn't fit)EndIfElse
            tmp_cell.EntireColumn.AutoFit 'Force fittingIf tmp_cell.ColumnWidth > cell.ColumnWidth Then'Cell doesn't fit!
                Fits = FalseExitFor'Exit For loop (at least one cell doesn't fit)EndIfEndIfNext
    tmp_cell.Worksheet.Delete 'Delete temporary Worksheet'Restore ScreenUpdating and DisplayAlerts state
    Application.DisplayAlerts = da
    Application.ScreenUpdating = su
EndFunction

Has solution got too complex, there may be some problems I didn't preview.

This won't work in read-only workbooks, however, cells in read-only workbooks don't change as well!

Find The Current User Language

2023-10-05T21:08:00.001-07:00

How can I tell the current user language in a vba program? I need this to show a form in an appropriate language.

Solution 1:

My initial code (utilising this vbforum code) assumed that Windows and Excel share a common language - likely but not bulletproof.

updated

The revised code:

Returns the Locale ID (LCID).
Looks up the LCID from this msft link.
Parses the LCID using a regexp to get the language.

Sample output on my machine below

The code will let the user know if there are any errors in accessing the LCID website, or in parsing the country name.

Sub GetXlLang()
        Dim lngCode AsLong
        lngCode = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
        MsgBox "Code is: " & lngCode & vbNewLine & GetTxt(lngCode)
    EndSubFunction GetTxt(ByVal lngCode) AsStringDim objXmlHTTP AsObjectDim objRegex AsObjectDim objRegMC AsObjectDim strResponse AsStringDim strSite AsStringSet objXmlHTTP = CreateObject("MSXML2.XMLHTTP")
        strSite = "http://msdn.microsoft.com/en-us/goglobal/bb964664"OnErrorGoTo ErrHandler
        With objXmlHTTP
            .Open "GET", strSite, False
            .Send
            If .Status = 200Then strResponse = .ResponseText
        EndWithOnErrorGoTo0

        strResponse = Replace(strResponse, "</td><td>", vbNullString)
        Set objRegex = CreateObject("vbscript.regexp")
        With objRegex
            .Pattern = "><td>([a-zA-Z- ]+)[A-Fa-f0-9]{4}" & lngCode                    
            If .Test(strResponse) ThenSet objRegMC = .Execute(strResponse)
                GetTxt = objRegMC(0).submatches(0)
            Else
                GetTxt = "Value not found from " & strSite
            EndIfEndWithSet objRegex = NothingSet objXmlHTTP = NothingExitFunctionErrHandler:IfNot objXmlHTTP IsNothingThenSet objXmlHTTP = Nothing
        GetTxt = strSite & " unable to be accessed"EndFunction

Solution 2:

dim lang_code aslong
lang_code = Application.LanguageSettings.LanguageID(msoLanguageIDUI)

Solution 3:

This is another variation of the code posted by brettdj

Sub Test_GetLocale_UDF()
Dim lngCode AsLong

lngCode = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
MsgBox "Code Is: " & lngCode & vbNewLine & GetLocale(lngCode)
EndSubFunction GetLocale(ByVal lngCode) AsStringDim html            AsObjectDim http            AsObjectDim htmlTable       AsObjectDim htmlRow         AsObjectDim htmlCell        AsObjectDim url             AsStringSet html = CreateObject("htmlfile")
Set http = CreateObject("MSXML2.XMLHTTP")
url = "https://www.science.co.il/language/Locale-codes.php"OnErrorGoTo ErrHandler
    With http
        .Open "GET", url, False
        .send
        If .Status = 200Then html.body.innerHTML = .responseText
    EndWithOnErrorGoTo0Set htmlTable = html.getElementsByTagName("table")(0)

ForEach htmlRow In htmlTable.getElementsByTagName("tr")
    ForEach htmlCell In htmlRow.Children
        If htmlCell.innerText = CStr(lngCode) Then
            GetLocale = htmlRow.getElementsByTagName("td")(0).innerText
            ExitForEndIfNext htmlCell
Next htmlRow

If GetLocale = ""Then GetLocale = "Value Not Found From " & url

ExitFunctionErrHandler:IfNot http IsNothingThenSet http = Nothing
GetLocale = url & " Unable To Be Accessed"EndFunction

Solution 4:

SelectCase Application.International(xlApplicationInternational.xlCountryCode) 
   Case1: Call MsgBox("English") 
   Case33: Call MsgBox("French") 
   Case49: Call MsgBox("German") 
   Case81: Call MsgBox("Japanese") 
EndSelect

Straight out of here: https://bettersolutions.com/vba/macros/region-language.htm

Relevant Documentation: https://docs.microsoft.com/en-us/office/vba/api/excel.xlapplicationinternational

Solution 5:

VBA Application.LanguageSettings.LanguageID(msoLanguageIDUI) gets only Microsoft Office Access or Microsoft Excel User interface Language. But does not reflect the Windows System display language used for Windows Display, every thing as MsgBox() buttons etc.

If user changes his Windows Display language via Windows Control Panel, this Application level setting will not change. In which case, we can use this Kernel dll function to get the new LCID (Microsoft Language Code IDentifier, 1036=French, 1033=English, ...), with this code in a VBA Module:

PrivateDeclareFunction GetUserDefaultUILanguage Lib"kernel32.dll" () AsLongPublicFunction winGetUserDefaultUILanguage()
  winGetUserDefaultUILanguage = GetUserDefaultUILanguage()
EndFunction

Then you can call the public function winGetUserDefaultUILanguage() every where in your VBA code to get Windows LCID.

The kernel dll function GetUserDefaultUILanguage() will reflect the changement via Windows Display Language ID.

For example, as I've French Windows display language, LCID=1036, my Office is also in French (LCID=1036),

VBA Debugger Console:

Now we switch to English Windows display language via Control Panel (LCID=1033 for English), but my Office language ID remains unchanged (LCID=1036):

VBA Debugger Console:

From LCID, you can further get Language Tag, Language Name from Microsoft API documents here or here.