Force Excel to run macro using SQL Query
I have a requirement to run the Excel file (either .xls or .xlsm) which has macros in it and it reduces the size of the other excel file and save it as different file in same folder. It works fine when I open the file manually from the folder, it runs immediately and creates the shrinked file.
But the requirement is to open the Excel from SQL query and run it. I am trying to do it with these two ways in my SQL query.
(1) EXEC [master].dbo.xp_cmdshell 'c:\temp\MyExcelShrink.xls' (2) EXEC xp_cmdshell 'dtexec /f c:\temp\MyPackage.dtsx'
The dtsx package has the same logic to open the Excel and run it, but none of these code working.
See also questions close to this topic
Why do I get datetime conversion out of range for 999 milliseconds?
Could someone please explain me, why I get the error
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
when I execute the following code on SQL Server 2017. I get the error only for .999 milliseconds
SELECT CAST('9999-12-31 23:59:59.999' AS DATETIME)
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Then I tried:
SELECT CAST('9999-12-31 23:59:59.998' AS DATETIME)
Result: 9999-12-31 23:59:59.997
Then I tried:
SELECT CAST('9999-12-31 23:59:59.997' AS DATETIME)
Result: 9999-12-31 23:59:59.997
ef core left join with "in" conditions
i have many ids of persons that i want see group is Assigned to this person، i have sql query like this
SELECT * FROM cor.[Group] AS g LEFT JOIN cor.PersonGroup AS pg ON g.Id=pg.GroupId AND pg.PersonId IN (1,2)
and i want to give linq query for this i write this linq query
from g in _context.Groups join pg in _context.PersonGroups.Where(pp =>personIds.Contains( pp.PersonId)) on g.Id equals pg.GroupId
But this is different from what I want
What does If Exists and If Not Exist Basically work?
I'm trying to understand how the the IF Exists and If NOT EXISTS working in SQL ? I've tried researching but still haven't got a clear concept of it. Can anyone post it in a simple way ?
I want to stop my QueryTable refresh from changing the column order on my table
I have created a table in Excel, linked to data in an Access database. On opening the Excel file, I give the user a choice to see if they need to refresh the data before performing other operations.
If they choose to refresh, the data updates, but re-arranges the columns, bringing some that I want to be kept at the right into the main section of data.
How do I prevent this? I have numerous other files that follow the same method, which don't re-order the columns.
If I run the refresh manually after right-mouse-clicking the data, it does the same thing, so I don't believe it's an issue with my code, but I still can't work out the issue.
I have ticked 'Preserve column sort/filter/layout' in the Properties dialog box.
If MsgBox("Update?", vbYesNo, "Update?") = vbYes Then Worksheets("Full Portfolio").Range("A6").ListObject.QueryTable.Refresh BackgroundQuery:=False End If
This code refreshes the table, but also re-orders columns that I want to remain in their current position.
How to fill column A based on another column?
I have 4 columns (Address1, Address2, and latlon).
Columns Address1 and Address2 are basically the same; except that Address1 contains the same addresses with Address2 multiplied N times.
The figure below shows a sample of the Address1 column:
Address1 Column Sample in Excel:
The 2nd figure is a sample of the dataframe with dropped repetitive addresses in column Address2:
Address 2 column with geocoded lat and lon:
I want to automatically place the lat and lon that matches with the address2 column to the address1 columns; however, they are not of the same length since address1 contains address2 multiple times
I recently took the column Address2 and dropped the repetitive addresses.
I then geocoded them (Address2) to get another column with latlon values.
Now, I want to place these latlon values into the original (Address1) column which contains more than 8000 rows.
Is there any function I could use directly with python to do this?
I am thinking of creating a new column for address1 such that each specific address is a number in the new column, then do the same for the address2 column and then match them (latlon) based on this? but, how do I code this in python once I create the new columns.
I am new to python and am still learning. Hoping someone could give some ideas or point me to the right way on how to go on with this.
Can you export ListObjects the same as ChartObjects from Excel to Powerpoint in VBA?
What I am looking for is a way how to export all Graphs named as ChartObjects along with all the tables that are named as Listobjects that are already created on Sheet("Output") of the Excel file to a Powerpoint file.
I have found ways to export ranges of tables to PowerPoint but as it is not set on a specific range each time I am having a harder time selecting it.
Also I am trying to keep it in the order of the Sheet"Output" as it is important to keep the order the same as all the sheets the graphs are generated from.
Public Sub ChartsToPpt() Dim sht As Object Dim cht As Excel.ChartObject Dim tbl As Excel.ListObject Dim AppPpt As Object 'PowerPoint.Application Dim prs As Object 'PowerPoint.Presentation 'Create New Presentation Set AppPpt = CreateObject("PowerPoint.Application") AppPpt.Visible = msoTrue Set prs = AppPpt.Presentations.Add(msoTrue) For Each sht In ActiveWorkbook.Sheets If sht.Visible = xlSheetVisible Then sht.Select Select Case LCase(TypeName(sht)) Case "worksheet" For Each cht In sht.ChartObjects cht.Select Application.CommandBars.ExecuteMso "Copy" PasteChart AppPpt, prs Next Case "chart" Application.CommandBars.ExecuteMso "Copy" PasteChart AppPpt, prs End Select Else sht.Select Select Case LCase(TypeName(sht)) Case "worksheet" For Each tbl In sht.ChartObjects tbl.Select Application.CommandBars.ExecuteMso "Copy" PasteChart AppPpt, prs Next Case "chart" Application.CommandBars.ExecuteMso "Copy" PasteChart AppPpt, prs End Select End If Next End Sub Private Sub PasteChart(ByVal PowerPointApplication As Object, _ ByVal TargetPresentation As Object) Dim sld As Object 'PowerPoint.Slide Dim cnt As Long Const ppLayoutBlank = 12 Set sld = TargetPresentation.Slides.Add( _ TargetPresentation.Slides.Count + 1, ppLayoutBlank) 'Add New Slide sld.Select cnt = sld.Shapes.Count PowerPointApplication.CommandBars.ExecuteMso "PasteExcelChartSourceFormatting" Do DoEvents Loop Until cnt <> sld.Shapes.Count End Sub Public Sub ChartsToPptAsImage() Dim sht As Object Dim cht As Excel.ChartObject Dim tbl As Excel.ListObject Dim AppPpt As Object 'PowerPoint.Application Dim prs As Object 'PowerPoint.Presentation 'Create New Presentation Set AppPpt = CreateObject("PowerPoint.Application") AppPpt.Visible = msoTrue Set prs = AppPpt.Presentations.Add(msoTrue) For Each sht In ActiveWorkbook.Sheets If sht.Visible = xlSheetVisible Then Select Case LCase(TypeName(sht)) Case "worksheet" For Each cht In sht.ChartObjects cht.CopyPicture Appearance:=xlScreen, _ Format:=xlBitmap PasteChartImage prs Next Case "chart" sht.ChartArea.Copy PasteChartImage prs End Select Else Select Case LCase(TypeName(sht)) Case "worksheet" For Each tbl In sht.ChartObjects cht.CopyPicture Appearance:=xlScreen, _ Format:=xlBitmap PasteChartImage prs Next Case "chart" sht.ChartArea.Copy PasteChartImage prs End Select End If Next End Sub Private Sub PasteChartImage(ByVal TargetPresentation As Object) Dim sld As Object 'PowerPoint.Slide Const ppLayoutBlank = 12 Const ppPasteBitmap = 1 Set sld = TargetPresentation.Slides.Add( _ TargetPresentation.Slides.Count + 1, ppLayoutBlank) 'Add New Slide With sld.Shapes.PasteSpecial(DataType:=ppPasteBitmap, Link:=msoFalse) .Height = 900 .Width = 900 .Align msoAlignCenters, True .Align msoAlignMiddles, True End With End Sub
I currently do not know how to export ListObjects to Powerpoint along with ChartObjects
Sending Mails through Execute SQL Task
I am trying to send the Mail from SSIS package. I tried using Send Mail Task but it is not sending mails to other domains like GMAIl, Yahoo, etc through the configured SMTP server. It is sending mails only to the company domain. Now, I am using a stored procedure which sends the mail to other domain which is configured by our DBA team. I am using a Variable in SSIS to store the emails from the table and trying to pass it to this Stored Procedure. But when it runs it only sends mail to the first record in the list and not the ones that follows. It is giving me an error as : [Execute SQL Task] Error: An error occurred while assigning a value to variable "InvoiceEmailTo": "Exception from HRESULT: 0xC0015005".
The stored procedure accepts multiple parameters like recipient, subject, body, etc. When you execute the stored procedure in SSMS, with multiple email IDs separated by a semicolon it works fine. I have tried appending semicolon to the email IDs stored in the table but still it is not working. Can someone please help into this?
I have tried every possible way to solve this but I am helpless. I am using an execute SQL Task to store the data in a variable as an object. I am passing that object in the For Loop container (For Each ADO Enumerator) and looping through the records and storing them in variable. This is where the Email IDs are getting stored in the Variable through Variable mapping which is passed to the Execute SQL Task inside the For loop which executes Stored procedure which sends email. For first record it is sending an Email but not for other records that follow.
The below query works fine in SSMS. EXEC msdb.dbo.sp_send_dbmail @recipients = 'email@example.com;firstname.lastname@example.org;', @subject = 'Email Notification For Invoice', @profile_name = 'SQL Mail' , @body = Dear , @body_format = 'HTML'
So for every parameter I am passing Variables over there as follows: "EXEC msdb.dbo.sp_send_dbmail @recipients = '" + @[User::InvoiceEmailTo] + "', @subject = '" + @[User::InvoiceEmailSubject] + "', @profile_name = '" + @[User::ProfileName] + "' , @body = '" + @[User::InvoiceEmailMessage] + "' , @body_format = '" + @[User::BodyFormat] + "'"
When I evaluated the above expression I am not getting any errors.
EXEC msdb.dbo.sp_send_dbmail @recipients = 'email@example.com', @subject = 'Email Notification', @profile_name = 'SQL Mail' , @body = 'Dear' , @body_format = 'HTML'
The package is sending the mail for the first EMAIL but for the second record it is failing.
Steps: EXECUTE SQL TASK -> Read Rows into a Variable FOR EACH LOOP -> Loop Over Rows Stored in the above Variable and mapping the required records according to the other Variables EXECUTE SQL TASK (Inside the For Loop) -> Sending Mails through the procedure.
I am not sure how the process is working. Whether it is sending all mails at once to the stored procedure or one by one.
Any help is appreciated.
how to handle project parameter in git release.
Details: I am using json as configuration. Deploying Ispac and json on ssis catalog version 2016.
How should I tokanize variable value for project parameter
How to properly write a DELETE FROM with a WHERE filter within Expression Builder in SSIS
I am trying to get a task to delete the data within my table for the last 35 days. So far I have been unsuccessful in getting this to run error free:
This is within the expression builder for my variable used to delete data in the designated table. Feel like I'm missing something that's probably really simple and would appreciate a second look
"DELETE FROM" + @[User::varCSTM_DET_REPEAT_CONTACTS] + "WHERE ContactStartDTM >= convert(varchar(10),getdate()-35,120)"
The error message received is:
Executing the query "DELETE FROMdbODS..CSTM_DET_REPEAT_CONTACTSWHERE C..." failed with the following error: "Incorrect syntax near 'ContactStartDTM'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Content cannot be enabled for .xlsm file generated with PhpExcel
I am trying to load and write contents on already existing .xlsm templates and save it as a new file in different directory.These files has macros ad the macros needs to be protected as it were in the original file. I have done the following things in php to get the result :
<?php error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); require_once dirname(__FILE__) . '/php_excel/Classes/PHPExcel.php'; require_once dirname(__FILE__) . '/php_excel/Classes/PHPExcel/IOFactory.php'; require_once dirname(__FILE__) . '/php_excel/Classes/PHPExcel/Reader/IReadFilter.php'; date_default_timezone_set('Europe/London'); $sheets = array('Welcome', 'Instructions', 'Data Definitions', 'Clothing'); $inputFileName = __DIR__ . '/templates/Clothing.xlsm'; $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $this->PHPExcelReader = PHPExcel_IOFactory::createReader($inputFileType); $sheetnames = $sheets; $this->PHPExcelReader->setLoadSheetsOnly($sheetnames); $this->PHPExcel = $this->PHPExcelReader->load($inputFileName); $this->PHPExcel->setActiveSheetIndex(3); // Redirect output to a client’s web browser (Excel2007) ob_end_clean(); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="test.xlsm"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($this->PHPExcel, 'Excel2007'); ob_end_clean(); $objWriter->save($this->filename); $this->PHPExcel->disconnectWorksheets(); unset($this->PHPExcel);
$this->filename is available in my class.
Everything works fine. I get exactly the same content as the original file but while enabling content i get
Runtime error '9': Subscript out of range.
I saw number of posts on github and wiki but didn't find what i needed. This was some how close but it had no answer or it just said phpexcel doesn't support xlsm file. If so , what is the best library that would support xlsm and macros things ? I have stucked in this since 3 days. Any kinds of suggestion and help are really appreciated.
running python xlwings library and it fails COMRetryObjectWrapper(DispatchEx('Excel.Application'))
I have a code which basically manipulates xlsm file, presses a macro button, and then force closing it (using psutil)
the process is: open xlsm file (MyExcel.xslm)==> write data ==> press macro button, save==> force close (with psutil).
and the process repeats 'x' times (lets say 20000 times)
this is the force close:
for proc in psutil.process_iter(): if proc.name() == "EXCEL.EXE": proc.kill()
and in a totaly random run, python stop running with the following error:
File "C:\Program Files\Python36\lib\site-packages\xlwings \_xlwindows.py", line 296, in __init__ self._xl = COMRetryObjectWrapper(DispatchEx('Excel.Application')) File "C:\Program Files\Python36\lib\site-packages\win32com\client\__init__.py", line 113, in DispatchEx dispatch = pythoncom.CoCreateInstanceEx(clsid, None, clsctx, serverInfo, (pythoncom.IID_IDispatch,)) pywintypes.com_error: (-2146959355, 'Server execution failed', None, None)
I am using python 3.6.6, xlwings =0.15.1
tried to add delays (time.sleep(1)), to add some time between runnings
it always failing in this line (last line):
def __init__(self): self.__number_of_cells = 48 self.new_file = os.path.join(folder, file)self.new_file= Myfile.xlsm self.workBook = xw.Book(self.new_file) # FAILS HERE!!
in this example, it only failed in run number =612.
sometimes it fails in 500.. and so on
How do I add image into .xlsm file?
I want to attach an image into a .xlsm excel file and the picture should be of original size, how can i achieve that?
This is what I have tried, but realize its not meant for xlsm,
string path_safe_graphic = AppDomain.CurrentDomain.BaseDirectory + @"PDI REPORT(MAN A95)Euro 6_" + Savestate.Bus_Number + ".xlsm"; Excel.Application xlsxApp_graphic = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook myExcelWorkbook_graphic; Excel.Worksheet xlsht_graphic = new Excel.Worksheet(); myExcelWorkbook_graphic = xlsxApp_graphic.Workbooks.Add(path_safe_graphic); xlsht_graphic = (Excel.Worksheet)myExcelWorkbook_graphic.Worksheets.get_Item(3); xlsht_graphic.Shapes.AddPicture(@"C:\Users\user\Desktop\PDI_APP_EDIT_FOR_TO - (Graphic changes)\PDIPROTOTYPE2\bin\Debug\pictures for app\All_SideSaved.PNG", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 30, 30, 64, 64); myExcelWorkbook_graphic.SaveAs(path_safe_graphic, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); myExcelWorkbook_graphic.Close();