VBForums - Office Development
https://www.vbforums.com/
Post all your questions here regarding Microsoft Office Automation. This can include VSTO, VSTA, and VBA questions.enWed, 17 Aug 2022 02:49:40 GMTvBulletin60https://www.vbforums.com/images/misc/rss.pngVBForums - Office Development
https://www.vbforums.com/
completely quit the created object in VBA excel
https://www.vbforums.com/showthread.php?897354-completely-quit-the-created-object-in-VBA-excel&goto=newpost
Tue, 16 Aug 2022 03:22:02 GMT run SPSS Syntax -> close SPSS object -> run next SPSS Syntax
so far, it can run the first Spss Syntax (*.sps file) very smoothly.
But the code seems not working well. It didn't close(quit) completely SPSS object.
and It can not run the second Spss'Syntax (the next *.sps file)
Is there any idea for my case?
How could completely close/quit/dispose SPSS created object?
Any idea is appreciated
Thank in advance.
Code:
---------
Sub runspsssyntax()
Dim i As Integer, link As String
Dim SPSS As spsswin.Application
For i = 0 To 40
link = "C:\Users\" & i & ".sps"
Set SPSS = GetObject(, "SPSS.Application")
SPSS.ExecuteInsert FileName:=link, syntaxBatch:=True, errorStop:=False, cd:=True, Sync:=True
SPSS.Quit
Set SPSS = Nothing
Next i
End Sub
---------
]]>Hi there.,

I'm doing a code for running Syntax of SPSS
by creating SPSS object in VBA excel -> run SPSS Syntax -> close SPSS object -> run next SPSS Syntax
so far, it can run the first Spss Syntax (*.sps file) very smoothly.

But the code seems not working well. It didn't close(quit) completely SPSS object.
and It can not run the second Spss'Syntax (the next *.sps file)

Is there any idea for my case?
How could completely close/quit/dispose SPSS created object?

Any idea is appreciated
Thank in advance.

Code:

Sub runspsssyntax()

Dim i As Integer, link As String
Dim SPSS As spsswin.Application

]]>Office Developmentmrhackgmhttps://www.vbforums.com/showthread.php?897354-completely-quit-the-created-object-in-VBA-excel<![CDATA[[RESOLVED] Counting and finding years]]>
https://www.vbforums.com/showthread.php?897315-RESOLVED-Counting-and-finding-years&goto=newpost
Fri, 12 Aug 2022 14:57:16 GMTI have a worksheet which contains 4 rows of data for each day of several months. Dates like 8/1/2022 are displayed in d-mmm-yy format like 1-Aug-22. I need to 1) determine how many months of data there are and 2) determine the address of the last row of the first month (or the address of the first row of the second month) so that I can cut the first month and paste it to a different sheet.
For 1) I know I can step through each row looking for a Day() value of 1 and then dividing by 4 (since there are 4 days 1s for each month) but I was hoping for something more elegant like a Worksheetfunction.countif with a wildcard search for the day 1s.I have a worksheet which contains 4 rows of data for each day of several months. Dates like 8/1/2022 are displayed in d-mmm-yy format like 1-Aug-22. I need to 1) determine how many months of data there are and 2) determine the address of the last row of the first month (or the address of the first row of the second month) so that I can cut the first month and paste it to a different sheet.

For 1) I know I can step through each row looking for a Day() value of 1 and then dividing by 4 (since there are 4 days 1s for each month) but I was hoping for something more elegant like a Worksheetfunction.countif with a wildcard search for the day 1s.
]]>Office DevelopmentMartinLisshttps://www.vbforums.com/showthread.php?897315-RESOLVED-Counting-and-finding-yearsPrintCommunication COM error
https://www.vbforums.com/showthread.php?897222-PrintCommunication-COM-error&goto=newpost
Tue, 02 Aug 2022 00:59:26 GMTI am having difficulty with the Application.PrintCommunication property with early binding.

In the code below, the first DirectCast statement executes without any issues but the second DirectCast statement raises a COM error.

With CType(xlSheet, Excel.Worksheet).PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.PrintArea = ""
End With '...PageSetup

'...setting PrintCommunication property to TRUE raises a COM error
DirectCast(xlSheet.PageSetup, Excel.PageSetup).Application.PrintCommunication = True

The error is "System.Runtime.InteropServices.COMException", which is not very helpful :(

Any insight/guidance on how to set the PrintCommunication property to True will be much appreciated.
]]>Office DevelopmentMark@SFhttps://www.vbforums.com/showthread.php?897222-PrintCommunication-COM-error<![CDATA[[RESOLVED] Permutations?]]>
https://www.vbforums.com/showthread.php?897116-RESOLVED-Permutations&goto=newpost
Mon, 25 Jul 2022 11:52:44 GMTI get confused between permutations and combinations, but anyhow I have ten columns, some of which may be blank while the rest contain one or two-digit numbers. Let's assume that the first row contains 4 numbers and that I can create this string "1,5,7,12" from those numbers. From that string, using VBA, I'd like to return these strings:
0105
0107
0112
0507
0512
0712

If my string were "1,5,7,12,33" I'd like to return
0105
0107
0112
0133
0507
0512
0533
0712
0733
1233

How would I do that?
]]>Office DevelopmentMartinLisshttps://www.vbforums.com/showthread.php?897116-RESOLVED-PermutationsVBSendMail and Access 64Bit VBA
https://www.vbforums.com/showthread.php?897078-VBSendMail-and-Access-64Bit-VBA&goto=newpost
Thu, 21 Jul 2022 10:37:20 GMTI use vbsendmail within access applications.
Office 64bit was installed on some new PCs.
When I try to instantiate the vbSendMail.clsSendMail class I get the "Interface not registered" error.

If, on the same pc, I uninstall 64-bit office and reinstall 32-bit office, vbsendmail works correctly.
I tried to follow several instructions to use components in a 64-bit Office environment but I could not solve the problem.

Anyone have any idea how to fix this?
Thank you,
Giorgio
]]>Office Developmentgiorrehttps://www.vbforums.com/showthread.php?897078-VBSendMail-and-Access-64Bit-VBA<![CDATA[[RESOLVED] PointsToScreenPixelsX]]>
https://www.vbforums.com/showthread.php?897053-RESOLVED-PointsToScreenPixelsX&goto=newpost
Tue, 19 Jul 2022 20:45:27 GMTI assume that PointsToScreenPixelsX comes into play if I want to align the top of my userform with the top of a cell, but what is the statement that does that? Would it be affected by either the position of the Excel window or the worksheet's zoom value?
]]>Office DevelopmentMartinLisshttps://www.vbforums.com/showthread.php?897053-RESOLVED-PointsToScreenPixelsX