Few important ms access VBA (DoCmd) objects

''********************** DoCmd objects in access VBA *********************************

'' This option will hide the alert messages like inserting/deleting any values in a table etc.
DoCmd.SetWarnings False

'' you can write down your sql queries but only DML queries will alloweded here"
DoCmd.RunSQL "Delete From Table_Employee"

'' By using this you can close any of the ms-access objects (like form, report, table, queries,macro, module etc.
'' change the acTable table parameter with any other above given access objects
DoCmd.Close acTable, "TableName", acSaveYes

'' By using this you can delete any of the ms-access objects (like form, report, table, queries,macro, module etc.
'' change the acTable table parameter with any other above given access objects
DoCmd.DeleteObject acTable, "TableName"

'' you can run/open any of your query object using this option (it require a Query object Name)
DoCmd.OpenQuery "QueryName"

'' By using this you can rename any of the ms-access objects (like form, report, table, queries,macro, module etc.
'' change the acTable table parameter with any other above given access objects
DoCmd.Rename "NewTableName", acTable, "OldTableName"

'' Hide ms access ribbon using Access VBA
DoCmd.ShowToolbar "Ribbob", acToolbarNo

'' Run your macros objects created in msacess
DoCmd.RunMacro "MacroName"

'' you can send your output automatically by the default email system (object Table, Query, form, Report etc.)
'' change the acSendTable table parameter and acFormatXLS as per your choice with any other above given access objects
DoCmd.SendObject acSendTable, "TableName", acFormatXLS, "abc@gmail.com", "xyz@yahoomail.com", "Test Email", "Hello"

'' Exit from msaccess
DoCmd.Quit acQuitSaveAll
Application.Quit acQuitSaveAll

Covert All the XLS file into XLSX format from the selected folder

Sub Convert_XLS_TO_XLSX()
Dim fLname As String
Dim folderPath As String
Dim fExtn As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    .Title = "please select folder"
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "folder not selected..", vbOKOnly + vbInformation, "Task Cancelled"
        Exit Sub
    Else
        folderPath = .SelectedItems(1)
    End If
End With

fLname = Dir(folderPath & "\")

While fLname <> ""
    fExtn = UCase(Right(fLname, Len(fLname) - InStrRev(fLname, ".")))
    If fExtn = "XLS" Then
        Workbooks.Open folderPath & "\" & fLname
        fLname = Left(fLname, InStrRev(fLname, ".")) & "xlsx"
        ActiveWorkbook.SaveAs folderPath & "\" & fLname, xlOpenXMLWorkbook
        ActiveWorkbook.Close True
    End If
    fLname = Dir
Wend

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "all the XLS files has been converted into XLSX format", vbOKOnly + vbInformation, "File Conversion Done"

End Sub

Import CSV file into Ms Access database and Convert into XML

'Access VBA code to import the CSV file and create a table and then export the table's data into XML File.
'' Note: Open access database -> press Alt+F11 (to open the VBA Editor) ->Insert->Module
'' Copy the below code and paste into the module
' Press F5 to run the below code
'********************************************************************************
Sub Convert_CSV_TO_XML()
    Dim fPath As String
    Dim fOutputFile As String
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "please select csv file"
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "file not selected...", vbOKOnly + vbInformation, "Task Cancelled"
            Exit Sub
        Else
            fPath = .SelectedItems(1)
            fOutputFile = CurrentProject.Path & "\"
        End If
    End With
   
    If fPath <> vbNullString Then
        DoCmd.TransferText acImportDelim, "", "Tbl_ImportData", fPath, True
        Application.ExportXML acExportTable, "Tbl_ImportData", fOutputFile & "\output.xml", fOutputFile & "\output.xsd"
        CurrentDb.Execute "Drop table Tbl_ImportData"
        MsgBox "file created in the below folder " & Chr(13) & fOutputFile, vbOKOnly + vbInformation, "XML file has been created"
    End If
End Sub

VBA code for unzip the files (multiple files can be selected)

Option Explicit
Sub Unzip_Files()
  Dim fCounter As Integer
  Dim UzipFolderPath As String
  Dim oApp As Object

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Filters.Add "Zip Files", "*.zip"
    .Title = "please select zip files"
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "file not selected", vbOKOnly + vbInformation, "Task Cancelled"
        Exit Sub
    Else
        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.Application")
     
        UzipFolderPath = CurrentProject.Path & "\UnZippedFiles"
        MkDir UzipFolderPath
        For fCounter = 1 To .SelectedItems.Count
            oApp.Namespace(UzipFolderPath & "\").CopyHere oApp.Namespace(.SelectedItems(fCounter)).items
        Next
    End If
End With
    Set oApp = Nothing
End Sub

'************************************************************************
'check this out and let me if it works.
Note: The above code has been written in msaccess
So, if you want to run this in excel. change the above syntax:
* CurrentProject.Path to thisworkbook.path

Merge any number of CSV files into single file within a second (Vbscript)

Sub Combine_CSV_Files

Dim fLocation, sLocation, dLocation, objShell

fLocation = InputBox("please enter the folder location")

If fLocation=vbNullString then
    MsgBox "folder not selected...", vbOKOnly + vbInformation, "Task Cancelled"
    Wscript.quit
End If

sLocation = fLocation & "\*.csv"
dLocation = fLocation & "\Combined_Data.csv"

Set objShell = CreateObject("WScript.Shell")
objShell.Run "cmd /k Copy """ & sLocation & """ """ & dLocation & """ & exit", False
Set objShell = Nothing

End sub

Call Combine_CSV_Files

*********************************************************************************
Copy the above code and paste into notepad file & save the notepad file with .vbs extension.

' Then click on the saved notepad file.
' it will prompt the folder path where all the CVS files stored.
' copy the folder path and paste into the input box
' all the cvs files has been combined within a second with the file name "Combined_Data.csv

Try this very useful..!

Access VBA methods to delete ms access objects

'' Access VBA methods to delete ms access object

'************** Delete your table or query
DoCmd.RunSQL "Drop table yourTableName or yourQueryName"
CurrentDb.Execute "Drop table yourTableName or yourQueryName"


'*** Below method will allow to delete any of the access objects (Table, Query, Form, Report) *****
DoCmd.DeleteObject acQuery, "YourQueryName"

'Select any of the below access object types that you want to delete
'acTable
'acQuery
'acForm
'acReport

Access VBA Methods to export your Table, Query, Form, Report data

'Access VBA methods to export the data

'*******************You can export the data from a table , query********************

'acSpreadsheetTypeExcel12Xml for xlsx file
'acSpreadsheetTypeExcel12 for xlsb format
'acSpreadsheetTypeExcel9 for xls format

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qRY_Final", "Destination file path"


'*****************************************************************************
'Below method will allow to export the table, form, report, query data into (Excel,txt,pdf) formats
'acFormatXLS
'acFormatTXT
'acFormatPDF
'acFormatRTF
DoCmd.OutputTo acOutputForm, "frm", acFormatTXT, "Destination file path & filename & fileextenstion"

'change the acOutput type as per your choice to select access object type
'acOutputTable
'acOutputForm
'acOutputReport
'acOutputQuery

Access VBA to Import data from Excel File

!......VBA program to Import data from Excel file & Creates a Table......!

Sub Import_From_Excel()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "New TablenName", "Source file path"
End Sub