Screenupdating Excel VBA

How to prenvent that excel visibly opens Openpyxl

2020.10.21 15:51 martinisi How to prenvent that excel visibly opens Openpyxl

I'm buiding a tool that relies for some part on an excel/vba tool. Now I build the whole python part to work with it. But i don't want excel to visibly open. Is there a way to prevent it like screenupdate in vba?
submitted by martinisi to learnpython [link] [comments]


2020.10.05 15:37 Sulprobil SystemState

SystemState
My friend and former colleague Jon T. created the smallest reasonable class module I have seen so far: SystemState provides an easy way to save and to restore system state variables like Calculation, ScreenUpdating and others.
Normally you write at the beginning of a VBA macro
Application.Calculation = xlCalculationManual Application.ScreenUpdating = False 
and at the end of a macro
Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True 
With the class module SystemState you just write at the start
Dim state As SystemState Set state = New SystemState 
and at the end
Set state = Nothing 'Not even necessary - will be done automatically 
[But remember: You cannot abbreviate this to "Dim state as New SystemState"!]
You can control a whole bunch of system state variables:

https://preview.redd.it/zdi88h6l4ar51.jpg?width=781&format=pjpg&auto=webp&s=0166a4b49334e19fa0125ca1559e7fc85df9cfcb
Option Explicit #Const ApplicationVersion = 16 'Get at runtime with Val(Application.Version) ' 'This class has been developed by my friend & former colleague Jon T. 'I just changed it slightly. Any errors are mine for sure. '03-Oct-2019 Bernd Plumhoff, Source: https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/classes/systemstate ' 'The class is called SystemState. 'It can of course be used in nested subroutines. ' 'This module provides a simple way to save and restore key excel 'system state variables that are commonly changed to speed up VBA code 'during long execution sequences. ' ' 'Usage: ' Save() is called automatically on creation and Restore() on destruction ' To create a new instance: ' Dim state as SystemState ' Set state = New SystemState ' Warning: ' "Dim state as New SystemState" does NOT create a new instance ' ' Those wanting to do complicated things can use extended API: ' ' To save state: ' Call state.Save() ' ' To restore state and in cleanup code: (can be safely called multiple times) ' Call state.Restore() ' ' To restore Excel to its default state (may upset other applications) ' Call state.SetDefaults() ' Call state.Restore() ' ' To clear a saved state (stops it being restored) ' Call state.Clear() ' Private Type m_SystemState Calculation As XlCalculation Cursor As XlMousePointer DisplayAlerts As Boolean #If ApplicationVersion > 15 Then EnableAnimations As Boolean 'From Excel 2016 onwards #End If EnableEvents As Boolean Interactive As Boolean #If ApplicationVersion > 12 Then PrintCommunication As Boolean 'From Excel 2010 onwards #End If ScreenUpdating As Boolean StatusBar As Variant m_saved As Boolean End Type ' 'Instance local copy of m_State? ' Private m_State As m_SystemState ' 'Reset a saved system state to application defaults 'Warning: restoring a reset state may upset other applications ' Public Sub SetDefaults() m_State.Calculation = xlCalculationAutomatic m_State.Cursor = xlDefault m_State.DisplayAlerts = True #If ApplicationVersion > 15 Then m_State.EnableAnimations = True #End If m_State.EnableEvents = True m_State.Interactive = True #If ApplicationVersion > 12 Then m_State.PrintCommunication = True #End If m_State.ScreenUpdating = True m_State.StatusBar = False m_State.m_saved = True 'Effectively we saved a default state End Sub ' 'Clear a saved system state (stop restore) ' Public Sub Clear() m_State.m_saved = False End Sub ' 'Save system state ' Public Sub Save(Optional SetFavouriteParams As Boolean = False) If Not m_State.m_saved Then m_State.Calculation = Application.Calculation m_State.Cursor = Application.Cursor m_State.DisplayAlerts = Application.DisplayAlerts #If ApplicationVersion > 15 Then m_State.EnableAnimations = Application.EnableAnimations #End If m_State.EnableEvents = Application.EnableEvents m_State.Interactive = Application.Interactive #If ApplicationVersion > 12 Then m_State.PrintCommunication = Application.PrintCommunication #End If m_State.ScreenUpdating = Application.ScreenUpdating m_State.StatusBar = Application.StatusBar m_State.m_saved = True End If If SetFavouriteParams Then Application.Calculation = xlCalculationManual 'Application.Cursor = xlDefault 'Dont - unnecessary Application.DisplayAlerts = False #If ApplicationVersion > 15 Then Application.EnableAnimations = False #End If Application.EnableEvents = False 'Application.Interactive = False 'Dont - dangerous #If ApplicationVersion > 12 Then Application.PrintCommunication = False #End If Application.ScreenUpdating = False Application.StatusBar = False End If End Sub ' 'Restore system state ' Public Sub Restore() If m_State.m_saved Then 'We check now before setting Calculation because setting 'Calculation will clear cut/copy buffer If Application.Calculation <> m_State.Calculation Then Application.Calculation = m_State.Calculation End If Application.Cursor = m_State.Cursor Application.DisplayAlerts = m_State.DisplayAlerts #If ApplicationVersion > 15 Then Application.EnableAnimations = m_State.EnableAnimations #End If Application.EnableEvents = m_State.EnableEvents Application.Interactive = m_State.Interactive #If ApplicationVersion > 12 Then Application.PrintCommunication = m_State.PrintCommunication #End If Application.ScreenUpdating = m_State.ScreenUpdating If m_State.StatusBar = "FALSE" Then Application.StatusBar = False Else Application.StatusBar = m_State.StatusBar End If End If End Sub ' 'By default save when we are created ' Private Sub Class_Initialize() Call Save(SetFavouriteParams:=True) End Sub ' 'By default restore when we are destroyed ' Private Sub Class_Terminate() Call Restore End Sub 
submitted by Sulprobil to Sulprobil [link] [comments]


2020.10.05 15:00 regxx1 A Class to Persist and Restore Application State

Not so long ago there was a post here requesting a way to persist the Application state and then be able to restore it at a later point. By "Application state" we mean, for example, the Calculation, DisplayAlerts, EnableEvents, and ScreenUpdating properties of the Application class. Several solutions were proffered - each having, IMO, their own pros and cons. However, each solution entailed putting the functionality into a class - the neat bit(s) being that the Application state would be persisted when an instance of the class was instanciated, and restored when the instance was finalised. For me, the coolest bit being that, even without any error handling, the state would always be restored. The only real con, if it can be described as such, was that none of the solutions encapsulated all of the possible Application properties. On a slight side not, I realised that there are several properties (Caption and StatusBar - there may be others) that can't simply have their current setting saved and then restored back to the saved setting. So I decided to have a stab at a solution that would allow the user (coder) to be able to add in at run-time their own preferred Application properties to be persisted and restored. I came up with the following:
'Class: ExcelApplicationState 'Beta release - still testing and under review 'Usage: ' ' Sub MySub() ' Dim eas As New ExcelApplicationState ' eas.Persist() 'This isn't necessary - already called in the Class_Initialize event ' eas.AddProperty "EnableAnimations", "EnableSound" ' Application.EnableAnimations = True ' Application.EnableSound = True ' . ' . ' . ' eas.Restore() 'This isn't necessary - will get called in the Class_Terminate event ' End Sub ' ' Option Explicit Dim m_isPersisted As Boolean Dim m_properties As Dictionary Public Sub AddProperty(ParamArray props() As Variant) Dim i As Long Dim prop As Variant For i = LBound(props) To UBound(props) prop = props(i) If (Not m_properties.Exists(prop)) Then m_properties.Add prop, Null If m_isPersisted Then PersistProperty prop End If End If Next i End Sub ' Not really necessary and probably not useful to most folk but handy for testing Public Function CurrentStateToString() As String Dim retVal As String retVal = "" Dim key As Variant For Each key In m_properties retVal = retVal & key & " = " & CallByName(Application, key, vbGet) & vbCfLf Next CurrentStateToString = Left$(retVal, Len(retVal) - 2) End Function Public Sub Persist() If (Not m_isPersisted) Then Dim key As Variant For Each key In m_properties PersistProperty key Next End If End Sub Public Sub Restore() If m_isPersisted Then Dim key As Variant For Each key In m_properties CallByName Application, key, vbLet, m_properties.Item(key) Next m_isPersisted = False End If End Sub ' Again, not really necessary but useful for testing Public Function ToString() As String Dim retVal As String retVal = "" Dim key As Variant For Each key In m_properties retVal = retVal & key & " = " & m_properties.Item(key) & vbCrLf Next ToString = Left$(retVal, Len(retVal) - 2) End Function Private Sub Class_Initialize() m_isPersisted = False Set m_properties = New Dictionary Me.AddProperty "Calculation", "Cursor" Me.AddProperty "DisplayAlerts", "DisplayFormulaBar" Me.AddProperty "EnableEvents" Me.AddProperty "ScreenUpdating", "StatusBar" Me.AddProperty "Visible" Me.AddProperty "WindowState" End Sub Private Sub Class_Terminate() Me.Restore Set m_properties = Nothing End Sub Private Sub PersistProperty(ByVal key As Variant) Dim v As Variant If key = "Caption" Then v = Mid$(Application.Caption, InStr(Application.Caption, "-") + 2) Else v = CallByName(Application, key, vbGet) v = IIf(UCase$(CStr(v)) = "TRUE", _ True, _ IIf(UCase$(CStr(v)) = "FALSE", _ False, _ v)) End If m_properties.Item(key) = v End Sub 
Discussion:
(1) What are your thoughts - good, bad, or ugly?
(2) I had an idea to allow Persist to be called multiple times and each time to push the current state onto a stack - subsequent calls to Restore would pop them back off of the stack. A worthwhile idea or complete overkill?
Edit: Fixed bugs - changed Exist to Exists and added missing line continuation characters - thanks to u/fanpages for spotting those mistakes.
submitted by regxx1 to excel [link] [comments]


2020.10.05 14:51 regxx1 [Excel] A Class to Persist and Restore Application State

Not so long ago there was a post here requesting a way to persist the Application state and then be able to restore it at a later point. By "Application state" we mean, for example, the Calculation, DisplayAlerts, EnableEvents, and ScreenUpdating properties of the Application class. Several solutions were proffered - each having, IMO, their own pros and cons. However, each solution entailed putting the functionality into a class - the neat bit(s) being that the Application state would be persisted when an instance of the class was instanciated, and restored when the instance was finalised. For me, the coolest bit being that, even without any error handling, the state would always be restored. The only real con, if it can be described as such, was that none of the solutions encapsulated all of the possible Application properties. On a slight side not, I realised that there are several properties (Caption and StatusBar - there may be others) that can't simply have their current setting saved and then restored back to the saved setting. So I decided to have a stab at a solution that would allow the user (coder) to be able to add in at run-time their own preferred Application properties to be persisted and restored. I came up with the following:
'Class: ExcelApplicationState 'Beta release - still testing and under review 'Usage: ' ' Sub MySub() ' Dim eas As New ExcelApplicationState ' eas.Persist() 'This isn't necessary - already called in the Class_Initialize event ' eas.AddProperty "EnableAnimations", "EnableSound" ' Application.EnableAnimations = True ' Application.EnableSound = True ' . ' . ' . ' eas.Restore() 'This isn't necessary - will get called in the Class_Terminate event ' End Sub ' ' Option Explicit Dim m_isPersisted As Boolean Dim m_properties As Dictionary Public Sub AddProperty(ParamArray props() As Variant) Dim i As Long Dim prop As Variant For i = LBound(props) To UBound(props) prop = props(i) If (Not m_properties.Exists(prop)) Then m_properties.Add prop, Null If m_isPersisted Then PersistProperty prop End If End If Next i End Sub ' Not really necessary and probably not useful to most folk but handy for testing Public Function CurrentStateToString() As String Dim retVal As String retVal = "" Dim key As Variant For Each key In m_properties retVal = retVal & key & " = " & CallByName(Application, key, vbGet) & vbCfLf Next CurrentStateToString = Left$(retVal, Len(retVal) - 2) End Function Public Sub Persist() If (Not m_isPersisted) Then Dim key As Variant For Each key In m_properties PersistProperty key Next End If End Sub Public Sub Restore() If m_isPersisted Then Dim key As Variant For Each key In m_properties CallByName Application, key, vbLet, m_properties.Item(key) Next m_isPersisted = False End If End Sub ' Again, not really necessary but useful for testing Public Function ToString() As String Dim retVal As String retVal = "" Dim key As Variant For Each key In m_properties retVal = retVal & key & " = " & m_properties.Item(key) & vbCrLf Next ToString = Left$(retVal, Len(retVal) - 2) End Function Private Sub Class_Initialize() m_isPersisted = False Set m_properties = New Dictionary Me.AddProperty "Calculation", "Cursor" Me.AddProperty "DisplayAlerts", "DisplayFormulaBar" Me.AddProperty "EnableEvents" Me.AddProperty "ScreenUpdating", "StatusBar" Me.AddProperty "Visible" Me.AddProperty "WindowState" End Sub Private Sub Class_Terminate() Me.Restore Set m_properties = Nothing End Sub Private Sub PersistProperty(ByVal key As Variant) Dim v As Variant If key = "Caption" Then v = Mid$(Application.Caption, InStr(Application.Caption, "-") + 2) Else v = CallByName(Application, key, vbGet) v = IIf(UCase$(CStr(v)) = "TRUE", _ True, _ IIf(UCase$(CStr(v)) = "FALSE", _ False, _ v)) End If m_properties.Item(key) = v End Sub 
Discussion:
(1) What are your thoughts - good, bad, or ugly?
(2) I had an idea to allow Persist to be called multiple times and each time to push the current state onto a stack - subsequent calls to Restore would pop them back off of the stack. A worthwhile idea or complete overkill?
Edit: Layout and formatting.
Edit: Fixed bug (changed Exist to Exists) - thank you to u/fanpages for spotting that.
Edit: Removed all instance of amp following a & character and added missing line continuation characters - thanks again to u/fanpages.
Edit: Had another stab at fixing the &/characters - thanks again to u/fanpages.
submitted by regxx1 to vba [link] [comments]


2020.09.24 12:21 Poison_Penis [EXCEL] VBA doesn’t work on a single workbook

Everything on my code works perfectly except for this one workbook. Literally nothing happens on this one stubborn ass workbook. My code opens the workbook, but from that point on literally regardless of what I try, when the code ends the selection will always be the same range (samerange).
My code can literally be
workbook(wb).open
‘Workbook opens
Range(“A1”).Select
And even that is ignored, the code ends and the workbook is open, with the selected range being (samerange). I’ve tried filtering, I’ve tried cells.find. I’ve put pauses. Screenupdating True. I have even deleted and redownloaded the workbook but still nothing works, short of having to bloody put an Exit there and select the range manually. This only happens in normal mode of course, it runs perfectly in debug. I think this is a sign from God, and I’m gonna quit Excel, quit VBA and go into the mountains to be a monk.
submitted by Poison_Penis to vba [link] [comments]


2020.07.31 13:51 lixenstrand Can hinder people from pressing Esc when getting data (power query) or running vba code?

Hey,

I've got an excel workbook with our stock list that will be sent out to customers and I want it as foolproof as possible. They have one excel sheet and then that just pulls data from our master excel workbook.

Now, I am using Application.screenupdating = False but I still get information when excel connects to the master file and then I get information that I can press Esc to cancel the pulling. This however stops the whole vba code and I'll come into the code.
How can I stop this from happening? I just want the code to finish running (it takes maybe 7 seconds or something)

Thanks in advance
submitted by lixenstrand to excel [link] [comments]


2020.07.13 14:02 thebrunospeed Index match running slow/crashing excel

Hi. I am performing index match across workbooks and worksheets. However, once I add in this part of VBA into a greater macro it takes longer to perform. A lot longer... as in excel tends to crash while otherwise its done within 2 seconds.
Is there a way to simplify the code? I already tried giving it a set range using last row however, this didn't work and gave me an error. I am also already using ScreenUpdating and Enable Events in an attempt to make it faster. Thank you.
Sub Search_Function() Dim sdb As Worksheet Set sdb = Workbooks("Snijbloemen Data Base.xlsx").Worksheets("Main Data Base") fplr = Sheets(2).Range("A2").End(xlDown).Row Dim k As Integer For k = 2 To fplr Cells(k, 10).Value = WorksheetFunction.Index((sdb.Range("C:C")), _ WorksheetFunction.Match(Sheets(1).Cells(9 + k, 10).Value, sdb.Range("A:A"), 0)) Next k End Sub 
submitted by thebrunospeed to excel [link] [comments]


2020.07.07 21:23 NoodleWhippet Excel VBA - What is the difference between these two .xlsx files?

Update 7/13: Test results are in, but unfortunately none of the recommendations have succeeded so far. The downstream tool still rejects WkbAdd w/ StringArray's, and .xls files. For now the work-around of creating the .txt and opening with Workbooks.openText is the only file the tool accepts. Very unusual issue. :( Thanks to everybody for your work on this, I appreciate the help! I hope somebody gets some use out of my random workaround in the future.
Update 7/9: Fantastic recommendations below - Thank you All! Testing is still ongoing. Hopefully I'll have the outcomes tomorrow 7/10. I'll post back as soon as I can. Thanks!
_________________
I create low-level automation solutions with Excel VBA. My new tool had to create a file for input into a different tool downstream. The .xlsx file my VBA code was creating kept failing the downstream automation. I found a solution/workaround, but I have no idea why it works and would like to learn more. Do you have any idea why Test 2 below works, but Test 1 does not? Edit: I am by no means a professional programmer, only self taught, and would welcome any suggestions to improve any part of the code.
The following code will generate two .xlsx save files based on the contents contained on Sheet1 of the ActiveWorkbook. Each save file is created a different way. The files will be saved to the users Desktop unless otherwise specified.
Can somebody educate me about the difference between these two files? Both files have identical cell values at the end of the tests. When I asked the developers of the downstream tool what the error was, they said their tool "Cannot even See" the Test 1 file - their tool returns a nullPointer error, and that's as much info as they could give me. I've taken a look at the XML but honestly have no idea what I'm looking at there - looks the same to me but I don't know the first thing about XML. I've been messing with different Sheet1 data and discovered something interesting about the way the files seem to store data for empty-cells. I do not know if this is why the downstream automation failed its only an observation. Edit // Additional Note: Whatever method used must preserve leading zeros in the data itself.
Started with random data in a 300x300 data range on Sheet1 - here are the results - pretty significant file size differences:
No empty data columns
100 empty data columns (with headers only)
200 empty data columns (with headers only)
Here is the code to run the tests and the steps to run it - it works for me in Excel 2013 and 365.

Option Explicit 'Required Reference Library: Microsoft Scripting Runtime 'This code creates two savefiles 'Each file contains the same data, contained on Sheet1 of the current workbook. 'Each file is created differently, and will have drastically different sizes. 'Recommended data on Sheet1 to clearly see a filesize difference: 300 columns and 300 rows of random junk-data. Public Sub CreateTwoSaveFiles() 'Turn off screenupdating Application.ScreenUpdating = False 'Change the final save location here, if you want. Dim FileLocation As String FileLocation = Environ("UserProfile") & "\Desktop\" 'The same data will be used to generate both tests. 'Add ~300 columns and ~300 rows of junk data to sheet 1 of this workbook. Dim ThisWks As Worksheet: Set ThisWks = ThisWorkbook.Sheets(1) Dim DataRng As Range: Set DataRng = ThisWks.Range("A1").CurrentRegion 'Read the data from the datarange, same data for all tests. Dim DataArr() As Variant: DataArr = DataRng.Value 'Create the filenames for Test 1 Dim FileName_WorkbooksAdd As String: FileName_WorkbooksAdd = "Test 1 - WorkbooksAdd.xlsx" Dim FullFile_WorkbooksAdd As String: FullFile_WorkbooksAdd = FileLocation & FileName_WorkbooksAdd 'Create the Filenames for Test 2 Dim FileName_TxtToExcel As String: FileName_TxtToExcel = "Test 2 - TxtToExcel.xlsx" Dim FullFile_TxtToExcel As String: FullFile_TxtToExcel = FileLocation & FileName_TxtToExcel 'Before the tests, Check to make sure the files generated do not already exist. If fileExistCheck(FullFile_WorkbooksAdd) = True Or _ fileExistCheck(FileName_TxtToExcel) = True Then MsgBox "Please delete prior test files and try again." GoTo ExitSub End If 'Test 1: Create SaveFile with Workbooks.add RunTest1_WkbAdd DataArr, FullFile_WorkbooksAdd 'Test 2: Create a text file with the data, then new workbook with Workbooks.openText RunTest2_TxtToExcel DataArr, FullFile_TxtToExcel MsgBox "Tests Complete" ExitSub: 'Turn On screenupdating Application.ScreenUpdating = True End Sub 'Creates a new workbook, adds the data to it, saves and closes the workbook. Sub RunTest1_WkbAdd(pDataArr() As Variant, pFullFile_WorkbooksAdd As String) 'Add a new workbook Dim WkbAdd As New Workbook: Set WkbAdd = Workbooks.Add Dim WksAdd As New Worksheet: Set WksAdd = WkbAdd.Sheets(1) 'Declare the output Range the same size as the input array With WksAdd Dim OutputRng As Range Set OutputRng = Range(.Range("A1"), .Range("A1").Offset(UBound(pDataArr, 1) - 1, UBound(pDataArr, 2) - 1)) 'To preserve leading zeros, make sure the data-range is formatted as text 'Note: In testing, this doubles the filesize or more if there are lots of empty cells in the input. OutputRng.NumberFormat = "@" 'Write the data to the range. OutputRng.Value = pDataArr 'OutputRng = pDataArr 'tried, same result. 'OutputRng.Value2 = pDataArr 'tried, same result. End With 'Now save and close the added workbook. With WkbAdd .SaveAs _ FileName:=pFullFile_WorkbooksAdd, _ FileFormat:=xlOpenXMLWorkbook 'StrictXML has the same result. .Close End With End Sub 'Writes the data to a .txt file, saves and closes it. 'Opens the text file as excel with workbooks.opentext, save and close the new workbook. Sub RunTest2_TxtToExcel(pDataArr() As Variant, pFullFile_TxtToExcel As String) 'Get the save location of the FullFilePath_TxtToExcel, we will be saving a temp text file to the same place. Dim FileLocation As String: FileLocation = Mid(pFullFile_TxtToExcel, 1, InStrRev(pFullFile_TxtToExcel, "\")) 'Create the temp text filename Dim FileName_TempTxtFile As String: FileName_TempTxtFile = "Test 2 - TempTxtToExcel.txt" Dim FullFile_TempTxtFile As String: FullFile_TempTxtFile = FileLocation & FileName_TempTxtFile 'Write the data to a tab-delimited text file WriteOutputToTXT_Tab pDataArr, FullFile_TempTxtFile 'Now open the text file as an Excel file TxtToExcel_SaveAs FullFile_TempTxtFile, vbTab, pFullFile_TxtToExcel 'Delete the temporary text file. Kill FullFile_TempTxtFile End Sub 'When sent a filepath+Name+Extention will check to see if the file already exists. Function fileExistCheck(FullFilePath As String) As Boolean Dim TestStr As String Dim FileExists As Boolean On Error Resume Next TestStr = Dir(FullFilePath) On Error GoTo 0 If TestStr = vbNullString Then FileExists = False 'The File Does Not Exist, TestStr is Blank Else FileExists = True 'TestStr is not blank, the file Does exist. End If fileExistCheck = FileExists End Function 'Opens a text file and saves it as excel. 'Note: The FSO functions Stream.ReadLine and Stream.AtEndOfLine sometimes failed for an unknown reason. 'As a work-around, the loop will scan each character in the text file until it detects an 'end-of-line character. If Stream.AtEndOfLine works, it will also exit the loop. Sub TxtToExcel_SaveAs(TxtFileLocation As Variant, txtDelimiter As String, ExcelFileLocation As String) Dim FSOText As FileSystemObject Set FSOText = New FileSystemObject 'Open the text file. Dim Stream As TextStream Set Stream = FSOText.OpenTextFile(TxtFileLocation, ForReading) 'Read the first line only, to find out how many columns there are in the data. Dim ChrCheck As String Dim ColCount As Long: ColCount = 1 'Must have 1 column at least. 'Detect how many columns are present in the data. 'This loop was necessary because Stream.AtEndOfLine and Stream.ReadLine sometimes failed. Do While Stream.AtEndOfLine <> True 'Read one character at a time from the stream. ChrCheck = Stream.Read(1) 'check to see if the character is a delimiter. If yes, add to the colcount value. If ChrCheck = txtDelimiter Then ColCount = ColCount + 1 'verify the character is not a newline character - if it is, it exits the loop. If ChrCheck = vbCr Or _ ChrCheck = vbCrLf Or _ ChrCheck = vbLf Or _ ChrCheck = vbNewLine Then Exit Do End If Loop 'Clost the Stream/textfile Stream.Close 'Create the 2-d Text Arrays for the Workbooks.openText method, FieldInfo parameter. Dim colArray() As Variant ReDim colArray(1 To ColCount, 1 To 2) 'The 2-d array will tell the Workbooks.openText method how to format the incoming data. Dim x As Long For x = 1 To ColCount colArray(x, 1) = x 'All columns are to be imported. colArray(x, 2) = xlTextFormat 'xlTextFormat = 2 is for Text Format Next x 'Open the text file in excel as all text format. Workbooks.OpenText _ FileName:=TxtFileLocation, _ DataType:=xlDelimited, _ Other:=True, _ OtherChar:=txtDelimiter, _ FieldInfo:=colArray Dim NewWkb As Workbook Set NewWkb = ActiveWorkbook NewWkb.SaveAs _ FileName:=ExcelFileLocation, _ FileFormat:=xlOpenXMLWorkbook NewWkb.Close End Sub Public Sub WriteOutputToTXT_Tab(ptOutput() As Variant, pFilePath_Name_Ext As String) Dim FSO As Scripting.FileSystemObject: Set FSO = New FileSystemObject Dim txtFile As Scripting.TextStream: Set txtFile = FSO.OpenTextFile(pFilePath_Name_Ext, ForAppending, True) 'Get the size of the output array Dim RowCount As Long: RowCount = UBound(ptOutput, 1) Dim ColCount As Long: ColCount = UBound(ptOutput, 2) 'Create a String that will hold one row of data to write to the text file. Dim OneRowStr As String 'For each row and column in the variant array, read its cell data Dim cRow As Long, cCol As Long Dim CellData As String For cRow = 1 To RowCount For cCol = 1 To ColCount 'read the data from the output array. CellData = ptOutput(cRow, cCol) 'add the cell data to the growing datastring, and add a Tab delimiter. OneRowStr = OneRowStr & CellData & vbTab Next cCol 'After the last column, add vbNewLine and Write one row at a time to the text file. txtFile.Write OneRowStr & vbNewLine 'Reset OneRowStr for the next loop. OneRowStr = vbNullString Next cRow 'Note: when dealing with huge datafiles, Reading the entire file as a 'single, giant String and then writing it to the text file once is extremely slow. 'The above method tested far faster for me. txtFile.Close Set FSO = Nothing End Sub 

submitted by NoodleWhippet to vba [link] [comments]


2020.01.31 17:16 Mysterious-Store Vba code running extremely slow not sure why

I’m making a Casino game within excel I’m fairly novice at vba but I think I’m getting the hang of it. All of a sudden my vba code is running super slow. Really not sure what I did to cause this I’ll type the code out this is for roulette game more games hopefully to come blackjack video poker slots Sub bet00_click() ‘This code runs when I click 00 on the board to place a bet
Call Speed_start ‘This is just simple function for turning calculations to manual and screenupdating to false If sheets(“Roulette”).Range(“S15”).value<= Sheets(“Roulette”).Range(“N14”).Value then ‘That checks to see if you have enough to make the bet Sheets(“roulette”).Range(“b2”).value = Sheets(“roulette”).Range(“b2”).value + Sheets(“roulette”).Range(“S15”).value ‘This places The bet amount into the list of bets to be calculated later after spin Call Total_bet ‘This is a function that just adds total amount bet to a cell Incase one wants to clear bets later or just see what they have currently bet
Sheets(“odds”).range(“money”).value = Sheets(“odds”).range(“money”).value - sheets(roulette).range(s15).value ‘ this just subtracts bet amount from cash stack
Else Msg box “you don’t have the cash to make that bet”,,”Error” End if Call speed_finish ‘ turns screenupdate back on and calc to auto End sub
Seems pretty simple not much going on. But for some reason it’s taking like 2-3 seconds to process. Any help would be awesome there are some typos and lazy typing of the code above I’m on my phone currently. But those typos are or missing “ and things are not the issue in the code.
Sorry for the layout also on phone so really not able to do it well
submitted by Mysterious-Store to vba [link] [comments]


2020.01.31 16:53 Mysterious-Store Why is my vba so slow? Help

I’m making a Casino game within excel I’m fairly novice at vba but I think I’m getting the hang of it. All of a sudden my vba code is running super slow. Really not sure what I did to cause this I’ll type the code out this is for roulette game more games hopefully to come blackjack video poker slots
 Sub bet00_click() ‘This code runs when I click 00 on the board to place a bet Call Speed_start ‘This is just simple function for turning calculations to manual and screenupdating to false If sheets(“Roulette”).Range(“S15”).value<= Sheets(“Roulette”).Range(“N14”).Value then ‘That checks to see if you have enough to make the bet Sheets(“roulette”).Range(“b2”).value =Sheets(“roulette”).Range(“b2”).value + Sheets(“roulette”).Range(“S15”).value ‘This places The bet amount into the list of bets to be calculated later after spin Call Total_bet ‘This is a function that just adds total amount bet to a cell Incase one wants to clear bets later or just see what they have currently bet Sheets(“odds”).range(“money”).value = Sheets(“odds”).range(“money”).value -sheets(roulette).range(s15).value ‘ this just subtracts bet amount from cash stack Else Msg box “you don’t have the cash to make that bet”,,”Error” End if Call speed_finish ‘ turns screenupdate back on and calc to auto End sub 
Seems pretty simple not much going on. But for some reason it’s taking like 2-3 seconds to process. Any help would be awesome there are some typos and lazy typing of the code above I’m on my phone currently. But those typos are or missing “ and things are not the issue in the code.
submitted by Mysterious-Store to visualbasic [link] [comments]


2019.10.28 08:20 ashrek1 How can I improve my excel vba code? long post sorry.

So I'm trying to make a custom add-in to; learn, save myself money and time. I've finished it and it works as I want it to but it's slightly slow. I'm super inexperienced with vba so I was hoping I could get help from some more experienced minds to help improve the code. Disclaimer most of it I got from tutorials on excel campus among other websites none of it is 100% my own code from scratch.

The first function is in a listbox userform to create a new workbook from selected sheets with values and formatting and add a table of contents then save it as "workbookname submission". this function works but is rather slow how could i optimise it? all the code is in a userform module.
Private Sub CommandButton1_Click() ScreenUpdating = False 'Create an array object to get the array of sheets to copy over Dim myarr() As String X = 0 'Look at every line on the Listbox and add to the sheet array For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then ReDim Preserve myarr(X) myarr(X) = ListBox1.List(i) X = X + 1 End If Next 'Get name of activeworkbook and create new workbook with name submission Dim fso As New Scripting.FileSystemObject Workbook_name = fso.getbasename(ActiveWorkbook.Name) ActiveWorkbook.Worksheets(myarr).Copy ActiveWorkbook.SaveAs Workbook_name & " submission.xlsx" new_workbook_name = fso.getbasename(ActiveWorkbook.Name) 'use values and formats For Each Sheet In Workbooks(Workbook_name & " submission").Worksheets Sheet.Activate ActiveSheet.UsedRange.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _ , SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Next Sheet Workbooks(new_workbook_name).Activate 'Adding the table of contents for the new workbook Call Table_Of_Contents.Create_TOC Workbooks(new_workbook_name).Save Unload Me End Sub Private Sub UserForm_Activate() 'Load Listbox with all workbook Sheet Names For i = 1 To ActiveWorkbook.Sheets.Count ListBox1.AddItem ActiveWorkbook.Sheets(i).Name Next i End Sub 
second function creates a table of contents with hyperlinks to each sheet. It's currently in a module because I want it to be for an add-in specifically. I want to know if it's possible i can make the table of contents update when a workbook with the table of contents is opened. I am aware i can use private sub worksheet_activate() to make it auto update if the code is in a worksheet module but I don't think i can do that because again i specifically want to make it an add-in.
Sub Create_TOC() Call test ActiveWorkbook.Worksheets("Table Of Contents").Activate Call TOC_List Call AutoFit End Sub Private Sub test() 'call function to create new worksheet if it doesn't exist If CreateSheetIf("Table Of Contents") Then Worksheets("Table Of Contents").Activate Call MoverToFront End If End Sub Function CreateSheetIf(strSheetName As String) As Boolean 'create a new worksheet if one with the selected name doesn't exist Dim wsTest As Worksheet CreateSheetIf = False Set wsTest = Nothing On Error Resume Next Set wsTest = ActiveWorkbook.Worksheets(strSheetName) On Error GoTo 0 If wsTest Is Nothing Then CreateSheetIf = True Worksheets.Add.Name = strSheetName End If End Function Private Sub MoverToFront() ActiveSheet.Move Before:=ActiveWorkbook.Sheets(1) End Sub Private Sub Worksheet_Activate() Call TOC_List End Sub Private Sub TOC_List() 'Create Table of Contents on this TOC sheet Dim ws As Worksheet Dim wsTOC As Worksheet Dim i As Long Application.ScreenUpdating = False 'Set variables Const bSkipHidden As Boolean = False 'Change this to True to NOT list hidden sheets Const sTitle As String = "A1" Const sHeader As String = "A2" Set wsTOC = ActiveWorkbook.Worksheets("Table Of Contents") 'can change to a worksheet ref if using in a regular code module i = 1 'Clear Cells wsTOC.Cells.Clear With wsTOC .Range("A1", "A1000000").Borders(xlEdgeRight).LineStyle = xlContinuous .Range("A1", "XFD1").Borders(xlEdgeBottom).LineStyle = xlContinuous End With 'Title With wsTOC.Range(sTitle) .Interior.Color = RGB(12, 2, 120) .Value = "Table of Contents" .Font.Bold = True .Font.Size = .Font.Size + 15 .Font.Color = RGB(255, 255, 255) End With 'Create TOC list With wsTOC.Range(sHeader) 'Create list For Each ws In ActiveWorkbook.Worksheets 'Skip TOC sheet If ws.Name <> wsTOC.Name Then 'Skipping hidden sheets can be toggled in the variable above If bSkipHidden Or ws.Visible = xlSheetVisible Then wsTOC.Hyperlinks.Add Anchor:=.Offset(i, 0), _ Address:="", _ SubAddress:="'" & ws.Name & "'!A1", _ TextToDisplay:=ws.Name i = i + 1 End If End If Next ws 'Turn filters off If wsTOC.AutoFilterMode Then wsTOC.Cells.AutoFilter End If 'Apply filters .Resize(i, 1).AutoFilter 'Formatting .Font.Italic = True 'AutoFit .Resize(i, 1).Columns.AutoFit End With Application.ScreenUpdating = True Call AutoFit End Sub Private Sub AutoFit() Dim X As Integer For X = 1 To ActiveSheet.UsedRange.Columns.Count Columns(X).EntireColumn.AutoFit Next X End Sub Private Sub Length() Range("G2:G10000").Formula = "=LEN(A2)" End Sub Private Sub format() ' ' format Macro ' ' Columns("G:G").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=0" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False End Sub 
sorry for the poor writing english isn't my strong suit.
Thanks for any help or advice.
tl-dr: vba noob wants to make his code better and is asking for help to do so because he doesn't know how to improve this.
submitted by ashrek1 to excel [link] [comments]


2019.10.02 11:47 don_cornichon Why does pasting values take so much longer in VBA than when doing it manually?

Example: I have a range of 15 columns and 13k rows, all filled with formulas (hlookup and the last column with hyperlinks).
When I manually paste it as values, either over itself or to a different sheet, that takes about half a second.
When I try to do this via VBA, I force quit excel after about 5 minutes of virtually no progress on the "Cells:" loading bar.
This happens whether I use [selection.pastespecial paste:=xlpastevalues] or [range1.value = range2.value], the latter being even slower despite being advertised as the "correct" way to do it on stackoverflow.
Does anyone know why it takes under a second to paste manually and an eternity with VBA, or what I'm doing wrong?
EDIT: Calculation and screenupdating are already off at this point.
EDIT2: Looks like by not copying the last column with a =hyperlink formula, at least I can paste it to a new sheet without issues, but overwriting the existing sheet with values still takes ages.
submitted by don_cornichon to excel [link] [comments]


2019.03.11 10:26 IliyaAngelov91 Application.screenupdating = False appears to cause a big slowdown

Hi all,

I have a very big excel file with over 20 000 rows of VBA code with lots of different macros, which are assigned to buttons and the whole excel filesize is about 4 MB using the xlsb format. At the beginning of every macro I have a the standard Application.screenupdating = False and calculations set to manual and at the end I turd them back on again. The problem is that after some use of the excel file I get massive slowdown which I can describe as a macro that usually takes 2-3 seconds takes 2-3 minutes and message boxes take like 20-30 seconds to appear and the text I put into the box takes like 10-12 seconds to appear. Buttons that have only OK option also take like 20-30 seconds to respond to the mouse click.
While running the code by hand by F5 in debug mode and not by button click the code also takes a lot longer to execute but the msgboxes and inputboxes work OK. Again it is slow after Application.screenupdating = False and the slowness is not there if I don't use it.

The only way I figured out after days of testing and debugging is to "remove" the slowness is to stop using Application.screenupdating = False at all and just leave it to true. Has anyone had the same issue? What causes it? Is it a bug with excel or vba?

note: I am using Excel 2013 on Windows 7
submitted by IliyaAngelov91 to vba [link] [comments]


2019.02.18 11:49 nekoakuma Speeding up VBA/Macro copy and paste by removing .select and .activate

Background:
  I have inherited a number of 5+ year old excel sheets with lots of macros. They look like they have been made via the record function in Excel (lots of .select statements). There is a certain macro which copies some information to another workbook, which runs a calculation, and then copies back select results to the original workbook. My VBA is lacking, I can sort of read through it and google struggle my way through the rest.
 
Goal:
  I want to speed up the macro. Current run time is 12min on average, with screenupdating = false. Effectively rendering my PC useless during that time and not letting me browse reddit. (I did get the original run time down by adding in screenupdating = false and displaystatusbar = false). And then commenting everything so the poor sucker after me can read it.
 
Current Code: Part of code only for reasons. I should be able to manage the rest. Basically it loops from X6 until the end (somewhere around x500 now), just copying pasting between certain cells in second workbook, and cells offset from X6 in original.
 
1 Application.ScreenUpdating = False 2 Application.DisplayStatusBar = False 3 Dim WB1 As Workbook 4 Set WB1 = Application.Workbooks("Calculation workbook path") 'update this line if version change 5 Range("k2:k3").Select 6 Selection.Copy 7 WB1.Activate 8 Range("B4").Select 9 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 10 :=False, Transpose:=False 11 ThisWorkbook.Activate 12 Range("x6").Select 13 Do Until ActiveCell.Value = "" 14 Selection.Copy 15 WB1.Activate 16 Range("B7").Select 'paste to calc 17 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 18 :=False, Transpose:=False 19 ThisWorkbook.Activate 20 ActiveCell.Offset(0, -21).Select 21 Application.CutCopyMode = False 22 Selection.Copy 23 WB1.Activate 24 Range("B8").Select 25 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 26 :=False, Transpose:=False 27 Range("I25").Select 28 Application.CutCopyMode = False 29 Selection.Copy 30 ThisWorkbook.Activate 31 ActiveCell.Offset(0, 16).Select 32 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 33 :=False, Transpose:=False 
 
What I need help with
New code:
Lines 19-26 becomes something like thisworkbook.ActiveCell.Offset(0, -21).copy _wb1.range(b8) Lines 27-33 becomes something like wb1.range(b8).copy _thisworkbook.activecell.offet(0,16) 
 
Basically removing all the .select and .activate statements. I'm pretty sure I need another dim for the thisworkbook/source workbook but my brain hurts.
Any help or pointers is much appreciated.
submitted by nekoakuma to excel [link] [comments]


2018.10.25 22:28 LehighLuke VBA chart animation no longer works in Excel 2016

I have some old files made in Excel 2013 that have nifty plot animation. Actually its a planetary gear set drawn in a chart, and it animates the rotation an meshing by updating a basic ANGLE value, and the rest of the series values update based on that.
This program and function worked great for years. Now I have Excel 2016, and I dusted this thing off for another job...and the animation doesnt work.

I can see the in-cell values all updating...if I increment that angle value outside of VBA it works, but when its running in the macro the chart just sits there static until its over, and then it redraws into the final position.

Before, I would just stick a command of selecting some cell in the sheet, and that would usually refresh the graph. That no longer works.
It won't work with Screenupdating = true, calculations = xlautomatic, doEvents, sleep...nothing works. I think its something specific to 2016, any ideas?
here is my code:
Sub MeshSim() Dim AStart#, AStep#, Ang#, NSteps As Integer Dim N As Integer, M& AStart = Range("B13") AStep = Range("B14") NSteps = Range("B15") Ang = AStart For N = 0 To NStep Sleep 200 Ang = Ang + AStep Range("B11").Value = Ang Range("D4").Select 'Application.ScreenUpdating = True 'Application.Calculation = xlCalculationAutomatic M = DoEvents 'Application.ScreenUpdating = False Next N Application.ScreenUpdating = True Range("B11") = 0 Range("A1").Select End Sub 
submitted by LehighLuke to excel [link] [comments]


2018.08.06 14:08 timopotato PasteSpecial function giving error 1004

Just starting to learn VBA and i've been trying tdifferent variations of this code to no effect. Basically, what I want it to do is run a query and then edit the info. I have a doevents line in there so excel waits for the backround query to start editing. the code gets as far as the "cut" function and throws this error:
Run time error '1004'
Pastespecial method of range class failed
Sub refresh() Application.screenupdating = false Activeworkbook.refreshall DoEvents with worksheets(2) .range("B18:Y30").Cut .range("Z3").Pastespecial paste:=x1PasteAllusingsourcetheme, operation:=x1None, skipblanks:=false, Transpose:=false .range("A18:A30").ClearContents .range("A16:Y17".unmerge Application.cutcopymode = false application.screenupdating = true end with end sub 
I had to type all this because my computer wont allow me to paste into here, and im also new to the new reddit layout, so if i missed something, I appologize.
the paste doesnt have to be a special function, it could just do a normal paste, but that doesnt work with the with worksheets(2) function, it just copies whatever is on worksheet 1.
Any help would be appreciated!
submitted by timopotato to excel [link] [comments]


2018.06.22 15:27 CFDragon06 Excel instance not closing on VBScript and VBA scheduled task

Sorry in advance for the long post but I wanted to provide as much detail as possible.
Running Excel 2016 32-bit. We have a large macro enabled excel file (65+ MB) with ~8 queries to other reports run and placed in a server location, along with many calculations to generate a dashboard report of active projects. We have two VBScripts that run through task scheduler. The first opens and runs macros to refresh all queries, then refresh each pivot table, and finally resize some reporting tables to match the new size of the pivot table. The second copies four sheets to a new excel file, pastes as data, generates a PDF of two of those sheets, then attaches both the new excel and the PDF to an email sent via VB script. I'll post as much of the codes as possible below.
All files are located on a server ('ME Based Server' in the codes below) and run locally on that server. The refresh script takes up to 15 minutes to run and the reporting script then takes ~ 4 minutes. This works without issue ~90% of the time.
The other 10% of the time, a few things may happen: * It may run successfully, but leave an instance of Excel open (probably in a non-responsive state) which is tying up more than 50% of the processing cores and ~490,000 KB of RAM. * It may run the refresh successfully, but fail on the report script which means we don't receive the automated email and it leaves an instance of Excel open. * It may fail during the refresh and leave an instance of Excel open.
This is scheduled 4 times a day and if one fails then each new run will open a new instance of Excel which will also fail but tie up more of the servers resources.
We're looking for advice to ensure that the instance of Excel closes even after failures to not bear down or overwork the server. Any advise will be greatly appreciated.
Refresh Code: Set objFSO = CreateObject("Scripting.FileSystemObject") Set oShell = WScript.CreateObject("WScript.Shell") On Error Resume Next Const DeleteReadOnly = True
strSourceFolder1 = "\\'US Based Server'\EngineeringDashboardReport\" strDestFolder1 = "\\'ME Based Server'\EngineeringDashboardReport\" Set colFiles1 = objFSO.GetFolder(strSourceFolder1) For Each objFile1 In colFiles1.Files If objNewestFile1 = "" Then Set objNewestFile1 = objFile1 Else If objNewestFile1.DateLastModified < objFile1.DateLastModified Then Set objNewestFile1 = objFile1 End If End If Next If Not objNewestFile1 Is Nothing Then objFSO.DeleteFile(strDestFolder1 & "*.xlsx"), DeleteReadOnly oShell.Run "xcopy.exe " & chr(34) & objNewestFile1 & chr(34) & " " & chr(34) & strDestFolder1 & chr(34) & " /R /Y" End If strSourceFolder2 = "\\'US Based Server'\EPT_BAH Revision Summary\" strDestFolder2 = "\\'ME Based Server'\EPT_BAH Revision Summary\" Set colFiles2 = objFSO.GetFolder(strSourceFolder2) For Each objFile2 In colFiles2.Files If objNewestFile2 = "" Then Set objNewestFile2 = objFile2 Else If objNewestFile2.DateLastModified < objFile2.DateLastModified Then Set objNewestFile2 = objFile2 End If End If Next If Not objNewestFile2 Is Nothing Then objFSO.DeleteFile(strDestFolder2 & "*.xlsx"), DeleteReadOnly oShell.Run "xcopy.exe " & chr(34) & objNewestFile2 & chr(34) & " " & chr(34) & strDestFolder2 & chr(34) & " /R /Y" End If strSourceFolder3 = "\\'US Based Server'\EPT_Weekly Forecast\" strDestFolder3 = "\\'ME Based Server'\EPT_Weekly Forecast\" Set colFiles3 = objFSO.GetFolder(strSourceFolder3) For Each objFile3 In colFiles3.Files If objNewestFile3 = "" Then Set objNewestFile3 = objFile3 Else If objNewestFile3.DateLastModified < objFile3.DateLastModified Then Set objNewestFile3 = objFile3 End If End If Next If Not objNewestFile3 Is Nothing Then objFSO.DeleteFile(strDestFolder3 & "*.xlsx"), DeleteReadOnly oShell.Run "xcopy.exe " & chr(34) & objNewestFile3 & chr(34) & " " & chr(34) & strDestFolder3 & chr(34) & " /R /Y" End If strSourceFolder4 = "\\'US Based Server'\ISO_Spool Summary\" strDestFolder4 = "\\'ME Based Server'\ISO_Spool Summary\" Set colFiles4 = objFSO.GetFolder(strSourceFolder4) For Each objFile4 In colFiles4.Files If objNewestFile4 = "" Then Set objNewestFile4 = objFile4 Else If objNewestFile4.DateLastModified < objFile4.DateLastModified Then Set objNewestFile4 = objFile4 End If End If Next If Not objNewestFile4 Is Nothing Then objFSO.DeleteFile(strDestFolder4 & "*.xlsx"), DeleteReadOnly oShell.Run "xcopy.exe " & chr(34) & objNewestFile4 & chr(34) & " " & chr(34) & strDestFolder4 & chr(34) & " /R /Y" End If Set objNewestFile1 = Nothing Set objNewestFile2 = Nothing Set objNewestFile3 = Nothing Set objNewestFile4 = Nothing MasterDB = "\\'ME Based Server'\BacklogTracker-BE.accdb" StaticDB = "\\'ME Based Server'\Query Lookup Data\BacklogTracker-BE.accdb" objFSO.DeleteFile StaticDB objFSO.CopyFile MasterDB, StaticDB Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.ScreenUpdating = True objExcel.DisplayAlerts = False objExcel.AskToUpdateLinks = False objExcel.AlertBeforeOverwriting = False extension = "xlsm" sourceDirectory = "\\'ME Based Server'\ENG PROD TRACKER" BahBLTracker = "\\'ME Based Server'\Backlog Tracker Query.xlsm" Set objWorkbook = objExcel.Workbooks.Open(BahBLTracker) objExcel.Calculation = xlManual objExcel.Run "'Backlog Tracker Query.xlsm'!RefreshAll.RefreshAll" objWorkbook.Save Set objWorkbook = Nothing Set objFolder = objFSO.GetFolder(sourceDirectory) For Each objFile In objFolder.Files If LCase((objFSO.GetExtensionName(objFile))) = LCase(extension) Then Filename = objFile.Name Filename = sourceDirectory & "\" & Filename Set objWorkbook = objExcel.Workbooks.Open(Filename) objExcel.Calculation = xlManual RefreshAll = "'" & objFile.Name & "'!RefreshAll.RefreshAll" ResizeTables = "'" & objFile.Name & "'!ResizeTables.ResizeTables" objExcel.Run RefreshAll objExcel.Run ResizeTables objExcel.Calculation = xlAutomatic objWorkbook.Save End If Next objExcel.Calculation = xlAutomatic objExcel.Quit Set objWorkbook = Nothing Set objFile = Nothing Set objFolder = Nothing Set objExcel = Nothing Set oShell = Nothing Set objFSO = Nothing 
Reporting Code:
'~~~Copy from Query file to final Excel & PDF report~~~ Dim objExcel, objWbTemp, objWbFin Dim tempFileName, sourceDirectory, extension, Filename, ReportFilename, PDFReportName Dim objFSO, objFolder, objFile On Error Resume Next Const DeleteReadOnly = True tempFileName = "\\'ME Based Server'\EngineeringReporting\Temp\temp.xlsm" ReportFilename = "\\'ME Based Server'\EngineeringProductionTracker\ENG PROD TRACKER\Reports to Email\Engineering Production Tracker " & Right("0" & DatePart("d", Now), 2) & " " & MonthName(Month(Now()),1) & " " & DatePart("YYYY", Now) & " Hour " & Right("0" & Hour(FormatDateTime(Now(),4)), 2) & " AST.xlsx" Set objExcel = CreateObject("Excel.Application") ' objExcel.ScreenUpdating = False 'When active, causes error on line "NOTED" below objExcel.Visible = True objExcel.DisplayAlerts = False extension = "xlsm" sourceDirectory = "\\'ME Based Server'\EngineeringProductionTracker\ENG PROD TRACKER\" Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder(sourceDirectory) objFSO.DeleteFile("\\'ME Based Server'\EngineeringProductionTracker\ENG PROD TRACKER\Reports to Email\*.xlsx"), DeleteReadOnly objFSO.DeleteFile("\\'ME Based Server'\EngineeringProductionTracker\ENG PROD TRACKER\Reports to Email\*.pdf"), DeleteReadOnly For Each objFile In objFolder.Files If LCase((objFSO.GetExtensionName(objFile))) = LCase(extension) Then Filename = objFile.Name Filename = sourceDirectory & Filename objFSO.CopyFile Filename, tempFileName Set objWbTemp = objExcel.Workbooks.Open(tempFileName) objWbTemp.Sheets("Engineering Tracker-SP").Select objExcel.Cells.Select objExcel.Selection.Copy objExcel.Selection.PasteSpecial -4163 '-4163 is VBS code for paste values objExcel.Range("C5").Select objWbTemp.Sheets("Engineering Tracker-LP").Select objExcel.Cells.Select objExcel.Selection.Copy objExcel.Selection.PasteSpecial -4163 '-4163 is VBS code for paste values objExcel.Range("C5").Select objWbTemp.Sheets("[email protected]").Select objExcel.Cells.Select objExcel.Selection.Copy objExcel.Selection.PasteSpecial -4163 '-4163 is VBS code for paste values objExcel.Range("A2").Select objWbTemp.Sheets("USENGOnHoldDetails").Select objExcel.Cells.Select objExcel.Selection.Copy objExcel.Selection.PasteSpecial -4163 '-4163 is VBS code for paste values objExcel.Range("A2").Select ' Make final report Excel File Set objWbFin = objExcel.Workbooks.Add() objWbFin.SaveAs(ReportFilename) objWbTemp.Sheets("Engineering Tracker-LP").Copy objWbFin.Sheets(objWbFin.Sheets.Count) objWbTemp.Sheets("Engineering Tracker-SP").Copy objWbFin.Sheets(objWbFin.Sheets.Count) objWbTemp.Sheets("[email protected]").Copy objWbFin.Sheets(objWbFin.Sheets.Count) objWbTemp.Sheets("USENGOnHoldDetails").Copy objWbFin.Sheets(objWbFin.Sheets.Count) objWbFin.Sheets("Sheet1").Delete ' Convert Summaries to PDF objWbFin.Sheets(Array("Engineering Tracker-LP", "Engineering Tracker-SP")).Select PDFReportName = Left(ReportFilename , InstrRev(ReportFilename , ".") - 1) & ".pdf" objWbFin.ActiveSheet.ExportAsFixedFormat 0, PDFReportName, 0, 1, 0,,,0 objWbFin.Sheets("Engineering Tracker-LP").Select ' "NOTED" line that errors when ScreenUpdating = False objExcel.Range("C5").Select ' Close and Cleanup objWbFin.Close True objWbTemp.Close False objFSO.DeleteFile tempFileName End If Next '~~> Close and Cleanup objExcel.Quit Set objWbFin = Nothing Set objWbTemp = Nothing Set objExcel = Nothing Set objFSO = Nothing Set objFolder = Nothing Set objWMIService = Nothing Set colItems = Nothing '~~~Send Report file in email~~~ ' Create link to CDOmail Set objEmail = CreateObject("CDO.Message") ' Enter subject of message in quotes objEmail.Subject = "Engineering Production Tracker " & Right("0" & DatePart("d", Now), 2) & " " & MonthName(Month(Now()),1) & " " & DatePart("YYYY", Now) ' Enter email address of sender within quotes ie objEmail.From = "sender" ' Enter destination email address in quotes. objEmail.To = "name" objEmail.Cc = "names" ' This is the email body. objEmail.HTMLBody = "This email contains the latest update to the Engineering Production Tracker Report.
This is an automated email.
Replies to this address will not be received." objEmail.AddAttachment ReportFilename 'NOTE: DO NOT USE AN "=" SIGN AFTER "AddAttachment" objEmail.AddAttachment PDFReportName 'NOTE: DO NOT USE AN "=" SIGN AFTER "AddAttachment" ' Also note replace mail.mcdermott.com and Port 25 with whatever your SMTP Server is using. Port is TYPICALLY 25 but some providers use a custom port objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = # objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "domain" objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = # objEmail.Configuration.Fields.Update objEmail.Send 'Wscript.Echo "DONE" 'Wscript.Quit
RefreshAll VBA Macro:
Sub RefreshAll() On Error Resume Next ActiveWorkbook.RefreshAll End Sub 
ResizeTables VBA Macro:
Sub ResizeTables() On Error Resume Next Dim RowCount As Long, LastRow As Long ' Resize USONHOLD Table RowCount = ThisWorkbook.Sheets("US ENG On Hold Details").Range("A4", ThisWorkbook.Sheets("US ENG On Hold Details").Range("A4").End(xlDown)).Rows.Count LastRow = ThisWorkbook.Sheets("USENGOnHoldDetails").Range("A1", ThisWorkbook.Sheets("USENGOnHoldDetails").Range("A" & RowCount + 1).End(xlDown)).Rows.Count ThisWorkbook.Sheets("USENGOnHoldDetails").Rows(3 & ":" & LastRow).Delete ThisWorkbook.Sheets("USENGOnHoldDetails").ListObjects("USOnHold").Resize ThisWorkbook.Sheets("USENGOnHoldDetails").ListObjects("USOnHold").Range.Resize(RowCount - 1) ' Resize US700NOH Table RowCount = ThisWorkbook.Sheets("US ENG @700 Not On Hold Details").Range("B4", ThisWorkbook.Sheets("US ENG @700 Not On Hold Details").Range("B4").End(xlDown)).Rows.Count LastRow = ThisWorkbook.Sheets("[email protected]").Range("A1", ThisWorkbook.Sheets("[email protected]").Range("A" & RowCount + 1).End(xlDown)).Rows.Count ThisWorkbook.Sheets("[email protected]").Rows(3 & ":" & LastRow).Delete ThisWorkbook.Sheets("[email protected]").ListObjects("US700NOH").Resize ThisWorkbook.Sheets("[email protected]").ListObjects("US700NOH").Range.Resize(RowCount - 1) ThisWorkbook.Sheets("Engineering Tracker-LP").Activate End Sub 
submitted by CFDragon06 to excel [link] [comments]


2017.08.03 02:57 Docssy [VBA] Improving user experience while moving data around sheets (must preserve formatting)

Hi!
I have developed a wicked system for work, but I'm noticing that speed is an issue.
I'm looking for one of two solutions:
  1. Increase speed.
  2. A way to cleanly advise clients of progress, without screen flicker.
Restrictions on my scenario:
  1. This is a shared workbook environment, with heavy VBA restrictions in place. That means I cannot take the easy way out by manipulating a covering shape properties (text, size, visibility)
  2. The setup is of a client GUI file, pulling and pushing data from a separate database excel file. Akin to a client/server system using a banker's transaction system. Success on all, or failure for all.
  3. I must bring rows across with formatting.
  4. The GUI holds the code.
  5. The database is the authoritative data source, so the GUI only holds a temp version of data.
How it works:
  1. When the GUI is run, or whenever the client requests to view the data, it checks a specific folder hiding in the user's appdata land for a dump file from another system.
  2. When a newer dump file is located (relative to the database's last updated timestamp for a specific dump), I initiate a database lock and update. This puts a wait on any other requests from other clients.
  3. When the database is locked for update by one client, I have to move a set of rows from 'active' to 'not active' rows.
  4. The dump file is analyzed, row by row for specific rows that match the criteria to be drawn into the database. Call it hits I guess.
  5. When a row hit is found....
  6. The row is copied to the 'active' sheet in the database.
  7. Logic happens to place specific columns from the dump file, into different columns in the database for that row (ie: dump col C becomes database col AB, col Z = col G, etc).
  8. A dump row may become a new row in the database if it does not exist but matches my criteria.
  9. Logic also occurs to apply formatting to individual columns based on good/bad/whatever
  10. This repeats until the dump file is consumed. Database timestamp is updated, the database is released and the dump file is deleted. Other clients will then be allowed to resume their work.
Normal operation for any client without an update, is to send the rows from the database 'active' sheet to a temporary working sheet in the GUI. Make changes, hit a button, and the database is locked to write back everything.
This dump file can easily stretch from columns A:BB or something The database has columns A:AD I think.
The issue at hand, is that the current method of sending rows between 'active' and 'not active' in the database during the processing of the dump file is via PasteSpecial, because I must preserve values and formatting. This is also the same method to send back and forth, rows from the database to the GUI.
The issue is speed. I'm dealing with dump files that can reach 3000-4000 rows in length - but I will generally only pull upwards of 200 specific rows into the database 'active' (ignoring the rest that fail criteria).
I need a solution that speeds up (replace) the process of PasteSpecial because it is painfully slow due to its inherent programming to select/copy/activate/select/paste.
The alternative, is a way to 'lock' user interaction with the shared GUI workbook AND the database so that the user cannot do anything with Excel while these operations are taking place, while instead giving them an updated progress (time to finish) to manage their expectations.
Currently ScreenUpdating is off, because with ScreenUpdating On Excel acts all stupid freezing for minutes at a time. Having it off, is the better option for this situation at this time, because the client can still see some form of responsiveness through virtue of the back-and-forth flickering/row adding/etc.
The other issue, and I miss this from standard VB6 / etc - is a reliable timer function to background these activities or continuously update the user on progress WITHOUT the stupid cursor flicking back and forth from busy circle to arrow millions of times. I've attempted a DoEvents loop but between the cursor and the notable chunky responsiveness of Excel while doing such, is not the experience I want.
I have thought about a forced always on top userform to provide the progress side of things which I can easily implement, but I haven't much of a clue so far as to handing the workbook flickering, nor the ability to have a consistent update screen AND the responsiveness of Excel intact.
I cannot provide a workbook example, for confidentiality reasons - but any advice is appreciated.
submitted by Docssy to excel [link] [comments]


2017.08.03 00:47 Docssy Improving user experience while copying rows (Format and Values must be Preserved)

Hi!
I have developed a wicked system for work, but I'm noticing that speed is an issue.
I'm looking for one of two solutions:
  1. Increase speed.
  2. A way to cleanly advise clients of progress, without screen flicker.
Restrictions on my scenario:
  1. This is a shared workbook environment, with heavy VBA restrictions in place. That means I cannot take the easy way out by manipulating a covering shape properties (text, size, visibility)
  2. The setup is of a client GUI file, pulling and pushing data from a separate database excel file. Akin to a client/server system using a banker's transaction system. Success on all, or failure for all.
  3. I must bring rows across with formatting.
  4. The GUI holds the code.
  5. The database is the authoritative data source, so the GUI only holds a temp version of data.
How it works:
  1. When the GUI is run, or whenever the client requests to view the data, it checks a specific folder hiding in the user's appdata land for a dump file from another system.
  2. When a newer dump file is located (relative to the database's last updated timestamp for a specific dump), I initiate a database lock and update. This puts a wait on any other requests from other clients.
  3. When the database is locked for update by one client, I have to move a set of rows from 'active' to 'not active' rows.
  4. The dump file is analyzed, row by row for specific rows that match the criteria to be drawn into the database. Call it hits I guess.
  5. When a row hit is found....
  6. The row is copied to the 'active' sheet in the database.
  7. Logic happens to place specific columns from the dump file, into different columns in the database for that row (ie: dump col C becomes database col AB, col Z = col G, etc).
  8. A dump row may become a new row in the database if it does not exist but matches my criteria.
  9. Logic also occurs to apply formatting to individual columns based on good/bad/whatever
  10. This repeats until the dump file is consumed. Database timestamp is updated, the database is released and the dump file is deleted. Other clients will then be allowed to resume their work.
Normal operation for any client without an update, is to send the rows from the database 'active' sheet to a temporary working sheet in the GUI. Make changes, hit a button, and the database is locked to write back everything.
This dump file can easily stretch from columns A:BB or something The database has columns A:AD I think.
The issue at hand, is that the current method of sending rows between 'active' and 'not active' in the database during the processing of the dump file is via PasteSpecial, because I must preserve values and formatting. This is also the same method to send back and forth, rows from the database to the GUI.
The issue is speed. I'm dealing with dump files that can reach 3000-4000 rows in length - but I will generally only pull upwards of 200 specific rows into the database 'active' (ignoring the rest that fail criteria).
I need a solution that speeds up (replace) the process of PasteSpecial because it is painfully slow due to its inherent programming to select/copy/activate/select/paste.
The alternative, is a way to 'lock' user interaction with the shared GUI workbook AND the database so that the user cannot do anything with Excel while these operations are taking place, while instead giving them an updated progress (time to finish) to manage their expectations.
Currently ScreenUpdating is off, because with ScreenUpdating On Excel acts all stupid freezing for minutes at a time. Having it off, is the better option for this situation at this time, because the client can still see some form of responsiveness through virtue of the back-and-forth flickering/row adding/etc.
The other issue, and I miss this from standard VB6 / etc - is a reliable timer function to background these activities or continuously update the user on progress WITHOUT the stupid cursor flicking back and forth from busy circle to arrow millions of times. I've attempted a DoEvents loop but between the cursor and the notable chunky responsiveness of Excel while doing such, is not the experience I want.
I have thought about a forced always on top userform to provide the progress side of things which I can easily implement, but I haven't much of a clue so far as to handing the workbook flickering, nor the ability to have a consistent update screen AND the responsiveness of Excel intact.
I cannot provide a workbook example, for confidentiality reasons - but any advice is appreciated.
submitted by Docssy to vba [link] [comments]


2016.12.20 11:54 DoktorDER csv workbook takes way too long to open up (vba)

Hi guys,
im a mechanical engineer from germany and use excel vba to make myself more comfortable with the planning of the machines
I get a lot of data from our internal system as .csv files which i can access through our interior network.
today i noticed that my program takes a lot longer than usually so i debugged the whole program to figure out why.
it seems that accessing one of the csv files takes a lot longer than accessing the other ones.
calculation/screenupdating etc. is all set to false
i use the workbook.open function to open the csv files.
if i open the csv files without having my big excel sheet open, it takes next to no time at all.
how can i find out what is going wrong? do you have any guesses?
submitted by DoktorDER to excel [link] [comments]


2016.11.07 21:52 tclean Access VBA formatting Excel Worksheet

Hey everyone, here's a weird one:
At my work we use Access, with a sql server backend. We have a roughly 4000 line formatted excel sheet that uses recordsets and vba formatting to insert about 15-20 mb of data into an excel worksheet.
When we load it up on Excel 2010 it takes roughly 2.5 minutes. Using 2013 or 2016 however, it takes in excess of 1.5 hours. I've spent days trying to find a real reason for this, as it seems to be a common issue when going from 2010 onward.
I stumbled upon a band aid solution which will not suffice. When loading the excel sheet, holding down left mouse button within either the verticle or horizontal scroll bars allows the data to load in at a much faster rate. This results in completion of the spreadsheet at roughly 4.5 minutes in Excel 2016, as opposed to over an hour without holding it down. 2010 again takes about 2.5 minutes.
I've considered adding some way to force scroll in the loops that generate cell data, however I'm not sure what the best practice would be in this scenario.
This is an extremely odd situation, and because the code belongs to my employer I am not allowed to post it. I'm just curious if anyone else has come up with the same or a similar problem in the past.
PS: I have manual calculation on and screenupdating off. These factors seem to have no influence on load time in Excel 2016.
Thanks for any input!
submitted by tclean to excel [link] [comments]


2015.11.16 19:40 theCANCELER Visual Bug caused by hiding/unhiding sheets

Hey guys first time poster. Moderate excel/vba skill here - Excel 2013. The project with the problem is used pretty much every day by multiple people.
I have inherited/ am trying to improve a spreadsheet that essentially takes data from one location and then passes it along via INDIRECTS to other locations. The INDIRECTS are used to create different inspection forms, all requiring the same base data. Operators who use this sheet can enter the data once (instead of up to 4 times), and then press a button which hides and unhides the appropriate sheets in the workbook based on the "form" they need at that point in time. Herein lies the problem.
There is a bug where after executing a hide/unhide, there seems to be a visual bug in which you'll enter a value on a sheet, and then part of the screen will bug out and show the data from the next sheet "below". You can see what the bug looks like here The operators, who are relatively unfamiliar with excel, have been hiding/uhiding, saving, closing the sheet, and then reopening it. When they do this, no visual bug, so I'm pretty certain that the act of hiding/unhiding is the root cause. That said, I don't know enough to be able to tell what's causing it or how to fix it.
As I mention in the album, scrolling down and up, or clicking out of excel onto another program or onto the desktop will cause the image to "refresh" and display correctly. Has anyone experienced anything like this before? Is this an issue with screenupdating somehow? Any advice would be immensely appreciated.
EDIT: So many thanks to everyone who took a look at this. I think I maybe have solved it by just going back to square one, throwing away what was there, and doing things the efficient way as shown below. As opposed to a hide/unhide callout for every sheet, I'm isntead using indexing, which after a lot of testing has had no issues. Would have saved myself a lot of time if I'd done it the smart way instead of trying to work with someone else's code haha. Again thanks to everyone who took a look.
The solution: Public inspecType As Integer
Sub PickYourForm_RoundedRectangle1_Click() 'first piece inspecType = 1 Call hider End Sub Sub PickYourForm_RoundedRectangle2_Click() 'Mass Inspection inspecType = 2 Call hider End Sub Sub PickYourForm_RoundedRectangle3_Click() 'incoming inspecType = 3 Call hider End Sub Sub hider() Dim a As Integer, b As Integer For x = 4 To 28 Sheets(x).Visible = False Next x If inspecType = 1 Then a = 4 b = 6 ElseIf inspecType = 3 Then a = 8 b = 11 ElseIf inspecType = 2 Then a = 12 b = 28 End If For y = a To b Sheets(y).Visible = True Next y Sheets(1).Activate End Sub 
submitted by theCANCELER to excel [link] [comments]


Application.ScreenUpdating a Application.Calculation Uso ScreenUpdating en Excel #1 way to make your VBA code go Faster by turning off ... EXCEL 44-Application ScreenUpdating Excel VBA  #06 Application Screenupdating Running VBA code faster with Screen Updating Method ... Excel VBA Introduction Part 40 - Disabling Screen Updates ...

  1. Application.ScreenUpdating a Application.Calculation
  2. Uso ScreenUpdating en Excel
  3. #1 way to make your VBA code go Faster by turning off ...
  4. EXCEL 44-Application ScreenUpdating
  5. Excel VBA #06 Application Screenupdating
  6. Running VBA code faster with Screen Updating Method ...
  7. Excel VBA Introduction Part 40 - Disabling Screen Updates ...

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link https://www.wiseowl.co.uk/donate?t=... 35 VBA Excel 2013 Application screen updating para evitar parpadeos de pantalla y aumentar la veloci - Duration: 4:15. Friccion Mental 11,596 views. 4:15. Excel VBA #06 Application Screenupdating Dominando o Excel ... Excel VBA: Esse módulo foi feito para quem quer automatizar tarefas repetitivas usando a programação Visual Basic for ... In this excel vba programming tips and tricks tutorial # 4, we are going to be learning how we can make our vba code faster or in other words how can we opti... Grab the Free VBA Quick Reference Guide https://chrisjterrell.com/p/getting-started Excel VBA Application Object Screenupdating and Calculation 35 VBA Excel 2013 Application screen updating para evitar parpadeos de pantalla y aumentar la veloci - Duration: 4:15. Friccion Mental 12,574 views. 4:15. Video tutorial perteneciente a Sergio Alejandro Campos, MVP Excel. Visita mi Blog para consultar más información sobre Excel y vba. http://blogs.itpro.es/exc...