Our training courses

Other training resources

Our training venues

Why we are different

Details for Chandra

Chandra has participated in the following threads:

Added by Chandra on 23 Feb 2024 at 07:11

Hello wiseowl,

Good Day!

inside folder have multiple files if open particular file have multiple sheets but I want to copy particular sheet data past into designation file using this code ,please help me

Option Explicit

Sub GetAllMovies()

Dim MyFilesPath As String

Dim MovieFileName As String

Dim cn As ADODB.Connection

Dim rsSheets As ADODB.Recordset

Dim i As Integer

Dim SheetList As Variant

Dim SQLString As String

Dim rsData As ADODB.Recordset

Sheet1.Range("A1").CurrentRegion.Offset(1, 0).Clear

MyFilesPath = ThisWorkbook.Path & "\My Files\"

MovieFileName = Dir(MyFilesPath & "*.xlsx")

Set cn = New ADODB.Connection

Set rsData = New ADODB.Recordset

Do Until MovieFileName = ""

'Debug.Print MovieFileName

cn.ConnectionString = _

"Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & MyFilesPath & MovieFileName & ";" & _

"Extended Properties='Excel 12.0 Xml;HDR=YES';"

cn.Open

Set rsSheets = cn.OpenSchema(adSchemaTables)

SheetList = rsSheets.GetRows(Fields:="TABLE_NAME")

rsSheets.Close

Set rsSheets = Nothing

For i = 0 To UBound(SheetList, 2)

SQLString = _

SQLString & " UNION ALL SELECT * FROM [" & SheetList(0, i) = "April" & "]" '& _

'" WHERE [Oscar Nominations] > 0 AND [Genre] = 'Animation' AND [Title] LIKE '%2'"

Next i

SQLString = Mid(SQLString, 12)

'Debug.Print SQLString

rsData.Open SQLString, cn

Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).CopyFromRecordset rsData

rsData.Close

SQLString = ""

cn.Close

MovieFileName = Dir

Loop

Sheet1.Range("A1").CurrentRegion.EntireColumn.AutoFit

End Sub

Added by Chandra on 22 Nov 2022 at 11:51

How to execute multiple stored procedure by using ADODB.

Ex: I have kept All stored procedure names in Column "A"

I am getting error, please help me, how to solution in below code

 

Public Sub Get_Result_From_Sp()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset


'Create and open a connection to the Staging Database
Set cn = New ADODB.Connection
       cn.ConnectionString = _
       "Provider=MSOLEDBSQL;" & _
       "Server=BDC7-L-D2V4JG3\SQLEXPRESS;" & _
       "Database=Bhanu;" & _
       "Trusted_Connection=yes;"
       
'try to open the connection, exit the subrouting if this fails
On Error GoTo Endpoint
cn.Open


'if anything fails after this point,close the connection before exiting
On Error GoTo closeconnection

' loop All procdure
Dim LastRow As Long
Dim i As Long
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow
'create and populate the command using the stored procedure
Set cmd = New ADODB.Command
       cmd.ActiveConnection = cn
       cmd.CommandType = adCmdStoredProc
       

    cmd.CommandText = i
       cmd.Execute (i)
       
'Create and populate the recordset using the SQLQuery
'Set rs = New ADODB.Recordset
'   Set rs = cmd.Execute

Next i

'=======================================================
'Error HANDLERS
 '======================================================
'if the recordset is opened successfully but a runtime error occurs later we end up here
closeconnection:
cn.Close

Set rs = Nothing
Set cn = Nothing

VBA.MsgBox _
       Prompt:="An occurred after the connection was established." & vbNewLine _
           & vbNewLine & "Error number: " & Err.Number _
           & vbNewLine & "Error description: " & Err.Description, _
           Buttons:=vbCritical, _
           Title:=" Error after connection open "
      Exit Sub
      
'if the connection failed to open we end up here
Endpoint:

VBA.MsgBox _
       Prompt:="the connection failed to open." & vbNewLine _
         & vbNewLine & "Error number: " & Err.Number _
         & vbNewLine & " Error description: " & Err.Description, _
         Buttons:=vbCritical, _
         Title:=" connection error"

End Sub

 

 

Added by Chandra on 27 Oct 2022 at 14:45

# concatenates all excel files in one worksheet

import pandas as pd
import glob

location = r"C:\Users\munichandra.kuppani\Documents\Pandas Experiments\MonthWiseData\*.xls"

excel_files = glob.glob(location)
pd.set_option('display.max_rows', 91)

# pd.set_option('display.max_columns',9)

df1 = pd.DataFrame()

for excel_file in excel_files:
    df2 = pd.read_excel(excel_file)
    df1 = pd.concat([df1, df2], ignore_index=True)

df1.fillna(value="N/A", inplace=True)
df1.to_excel(r"C:\Users\munichandra.kuppani\Documents\Pandas Experiments\MonthWiseData\combined_excel.xlsx")
print(df1)

i am getting this error, please give me solution :

[Running] python -u "c:\Users\munichandra.kuppani\Documents\Pandas Experiments\all_sleep_data.py"
  File "c:\Users\munichandra.kuppani\Documents\Pandas Experiments\all_sleep_data.py", line 6
    location = 'C:\Users\munichandra.kuppani\Documents\Pandas Experiments\MonthWiseData\*.xls'
                                                                                              ^
SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape
[Done] exited with code=1 in 0.468 seconds

 

Added by Chandra on 17 Feb 2022 at 12:32

It has not activated, please be let me, what do I do?


At line:1 char:1
+ .\Activate.ps1
+ ~~~~~~~~~~~~~~
    + CategoryInfo          : SecurityError: (:) [], PSSecurityException   
    + FullyQualifiedErrorId : UnauthorizedAccess
PS I:\Wiseowl-Python\Tutorial\scripts>

Added by Chandra on 10 Feb 2022 at 11:45

Traceback (most recent call last):

  File "i:\Wiseowl-Python\File handling.py", line 4, in <module>

    File=open(r"C:\Users\Munichandra Kuppani\Downloads\Files needed\Best films ever.text","r")

FileNotFoundError: [Errno 2] No such file or directory: 'C:\\Users\\Munichandra Kuppani\\Downloads\\Files needed\\Best films ever.text'

if you don't  mind , would you please tell me solution.

Added by Chandra on 22 Oct 2021 at 14:15

I am unable to create files in Path, I am getting runtime error, please see the below code

 

Sub SplitTable()

    Dim MovieFilePath As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    
    
    
    MovieFilePath = ThisWorkbook.Path & "\Movies.xlsx"
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & MovieFilePath & ";" & _
        "Extended Properties='Excel 12.0 Xml;HDR=YES';"
    
    cn.Open
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = cn
    rs.Source = " Select DISTINCT [Country]  From  [Film$]   "
    rs.Open
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    
    Do Until rs.EOF
'           Debug.Print rs.Fields("Country").Value
           cmd.CommandText = _
                                           " Select *  " & _
                                           " Into [" & rs.Fields("Country").Value & "]" & _
                                           " In ' " & ThisWorkbook.Path & " \ Countries Data \  " & rs.Fields("Country").Value & " .xlsx'   'Excel 12.0 Xml; ' " & _
                                           " From [Film$] " & _
                                           " Where [Country]=  ' " & rs.Fields("Country").Value & " ' "
                                           
'     Debug.Print cmd.CommandText
    cmd.Execute
    rs.MoveNext
    Loop
    rs.Close
    cn.Close
    
End Sub

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...