Loading...

Follow Excelgoodies - Insights from Microsoft Excel Power Users on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


Here are the primitive commands to open an XML file in Microsoft Excel.

Sub Open_XMLFile()
Dim OXML As Workbook
Set OXML = Workbooks.OpenXML(“c:\sample.xml”)
End Sub

Sub Open_XML_File_As_List()
Dim OXML As Workbook
Set OXML = Workbooks.OpenXML(Filename:=”c:\sample.xml”, LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList)
End Sub

This option will work for Excel 2003 and above

Explore comprehensive Excel VBA Macro Programming here and, learn to automate your routine Excel Reports, Processes and Tasks.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Vlookup:

The syntax for Vlookup is: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Index:

The Syntax for Index is:

INDEX( array, row_number, [column_number] )

Match:

The Syntax for Match is:

MATCH( value, array, [match_type] )

Problem:

For Example:

Using Vlookup In the above table we can find EmployeeName & Dept with the help of EmployeeNumber and Dept with the help of EmployeeName, however we are not able to find EmployeeNumber with EmployeeName and Dept because the Vlookup will work only from the right to its value and not to the left.

To fix this issue we use Index & match function.

Solution:

Use Index first and insert Match function into it.

=Index(Data Range, #,Column number)

“#” indicates the row number to which it is referring to. So use Match function instead of “?”

=Index($A$2$:$A$4,Match(A6,$B$2:$B$6,False),1)

The number which is in the last in the index can help you with EmployeeNumber for 1, EmployeeName for 2 and Dept for 3.

Explore 60+ Functions here and become a Microsoft Excel Power User.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The below code merges around 50 ascii files into one

It uses File System Object and you need to add a reference of Microsoft Scripting Runtime

Sub Append_Text_Files()

Dim F1 As FileSystemObject
Dim F2 As FileSystemObject

Dim oTS As TextStream
Dim oTS1 As TextStream

Dim vTemp

Set F1 = New FileSystemObject
Set F2 = New FileSystemObject

For i1 = 1 To 50

Set oTS = F1.OpenTextFile(“c:\Sheet” & i1 & “.txt”, ForReading)
vTemp = oTS.ReadAll

Set oTS1 = F1.OpenTextFile(“c:\CombinedTemp.txt”, ForAppending, True)
oTS1.Write (vTemp)

Next i1

End Sub

Explore comprehensive Excel VBA Macro Course here and, automate your routine Excel Reports, tasks and processes.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

All you have to do just enter “reverse” function in a cell and refer to the cell in which you have text which you want to reverse.

Public Function reverse(ByVal cell As Range) As String
rvrse = VBA.strReverse(cell.Value)
End Function

Do wonders in Automation using VB Programming with our comprehensive Microsoft Excel VBA Course here.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Just select a range and run this code, excel will speak all the text what you have in that range, cell by cell.

Sub Speak()
Selection.Speak
End Sub

Do wonders in Automation with our comprehensive Excel VBA Programming Course here.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The below code will let you save each Worksheet as a single PDF

Sub SaveWorkshetAsPDF()
Dimws As Worksheet
For Each ws In Worksheetsws.ExportAsFixedFormat xlTypePDF, “ENTER FOLDER NAME HERE" & ws.Name & ".pdf" Nextws
End Sub

Explore comprehensive Excel VBA Course here and, learn to automate your routine Excel reports, processes and tasks.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

There’s a calculator in Excel 2010 but by default this feature is hidden, to get the calculator on your Excel screen navigate to Quick Access toolbar options – it’s the down arrow symbol next to the undo button on the top left-hand of the screen – and click More Commands.

Adding a calculator hot link to Excel

It will lead you to Excel Options dialog, under Choose commands, click drop-down button to select All Commands. Now scroll-down to find the Calculator command and click Add >> to show it in Quick Access toolbar. Hit Ok to continue

Click Calculator button in Quick Access toolbar to immediately open Calculator. Now you can use it for simple calculations.

Explore comprehensive features of Microsoft Excel here.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The below code will add serial numbers up to 500 in your sheet and along with it will show progress on the status bar.

Sub progressStatusBar()
Application.StatusBar= "Start Printing the Numbers"
For icntr= 1 To 500
Cells(icntr, 1) = icntr
Application.StatusBar= " Please wait while printing the numbers " & Round((icntr/ 500 * 100), 0) & "%"
Next 
Application.StatusBar= ""
End Sub

Explore extensive VBA Automation Course here, to automate your routine Excel Reports, Processes and Tasks using Microsoft Excel VB Programming.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The below code will check each cell from the selection and highlight the cell where is a misspelled word.

Sub HighlightMisspelledCells()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If Not Application.CheckSpelling(word:=rng.Text) Then
rng.Style= "Bad" End If
Next rng
End Sub

Explore extensive Excel VBA Programming Course here and, learn to automate your routine Excel Reports, Tasks and Processes.

Happy Excelling!
Team Excelgoodies

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

PDF is always the universal format for sending the files. With lot of versions of MS Office and other Office suites around, it is always better to circulate the Deck as a PDF

The following snippet converts the Presentation to a PDF and saves in the same folder of the PPT

ActivePresentation.ExportAsFixedFormat ActivePresentation.Path & “\” & ActivePresentation.Name & “.pdf”, ppFixedFormatTypePDF, ppFixedFormatIntentPrint

Learn to write VB Coding or Macros in Excel and, automate your routine task with our specialized course on Excel Automation here.

Happy Excelling!
Team Excelgoodies
www.Excelgoodies.com

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview