Details for Chandra

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

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

# 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

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>

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.

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