Search the Community
Showing results for tags 'vba'.
-
Hi, I'm looking to be able to resize a table and then fill the table cells with data from another table in a custom order. This is how I resize the table Sub Resize_Table() Dim rng As Range Dim tbl As ListObject Set rng = Range("DisplayTable[#All]").Resize(Range("E11") + 4, Range("D11") + 1) ActiveSheet.ListObjects("DisplayTable").Resize rng End Sub Is there a way to fill the cells with data from another table?
-
Typical task for me: I have a list of names of 50 local businesses and I need to find out their mailing address. I usually google them manually and copy/paste their address into my excel file. There's got to be a better way. My research shows several ways including google places api, vba code, excel power query. But the info is overwhelming and I'm not really smart enough to know what most of it means... My skill level is I can copy/paste VBA code and create various macro functions in excel. Any insight would be great. I'm willing to do my own research, just need to go down the right path, and not 100 dead ends based on my coding skill level. Thanks, Scrim Snacks
-
- google api
- xml
-
(and 2 more)
Tagged with:
-
Hi, I have a new student job and the task assigned to me is to empty option boxes in an excel documents on the onedrive of the company so they can be filled out again by the employees. The only problem is that that that each excel document has 12 tabs and that each tab contains more than 70 option boxes. I have to empty 40 such documents a week (one doc per employee). Is there a way to empty all option boxes (make them blank again) with a macro? The help is much appreciated
-
I have tried a lot of approaches. But can't seem to make this work: I'm creating a UI in excel. A cmd-button on sheet1 opens a userform. Another cmd button (in this userform) allows the user to add a row of new fields in the form along with a cmd button to delete the row - if so required. It is this cmd 'delete button' I'm having issues with. The delete buttons are created, and the code inserted into "userform1", but does not execute when I click on the newly created cmd buttons. Here is my code: Option Explicit Private Sub UserForm_Initialize() Dim arrNames, arrCaptions As Variant Dim NewBtn As Control Dim Code As String Dim NextLine, LeftPos, Gap, i As Long Dim objForm As Object Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1") arrNames = Array("cmbName1", "cmbName2", "cmbName3") LeftPos = 100 Gap = 15 For i = LBound(arrNames) To UBound(arrNames) Set NewBtn = UserForm1.Controls.Add("Forms.CommandButton.1") With NewBtn .Left = LeftPos .Top = 5 .Width = 65 .Height = 30 .Name = arrNames(i) .Visible = True .Caption = "Delete row " & i + 1 .Font.Size = 10 .Font.Name = "Times New Roman" End With 'Add the event handler code which does not seem te work at the moment 'The code is added to "userform1" but does not execute when clicking on the new command buttons Code = "Sub " & NewBtn.Name & "_Click()" & vbCrLf Code = Code & " MsgBox ""action for cmd button"" & i" & vbCrLf Code = Code & "End Sub" With objForm.CodeModule 'Find last line in Codemodule NextLine = .CountOfLines + 1 .InsertLines NextLine, Code End With 'NEXT button's pos. LeftPos = LeftPos + NewBtn.Width + Gap Next i End Sub Please advise...
-
Email Notification When Changes Made to Network Folder
chandlermaxwell posted a topic in Programming
I'm working as a graduate assistant this semester and was tasked with helping figure out how to get a process more automated within my department. I am an accounting major so my computer programming experience is next to zero. I am trying to figure out the best way to have email notifications sent to folks when files are added from to a network folder. We are using PDF fillable forms and they get one process completed in one network folder by an employee and then get copied over to another network folder for the next person to complete their portion. It's at that point that I'd like the system to send an email notification maybe every hour or something if there have been additions to the folder. I don't really care about email notifications for file deletion. The problem I've run into so far with solutions I've found online is that my organization uses Gmail for our email services and every batch file I've found requires the usage of Outlook or some other type of SMTP type email set up. Thanks -
Hello there my friends, I'm currently administrating and managing the MS Exchange 2013 Server down here the company where I'm working at. But I've ran into a quit huge problem for our / my workflow. Now a bit more details about the problem itself: I wanted to write a Script in Visual Basic for Applications (VBA) which does the following things: I have to enter 2 Dates, one counting as the "start" and the other one counting as the "end" of a certain time period. If I confirm them by clicking on another button labeled "Confirm", it should convert the 2 Dates, into a String-variable and combine the variable with a pre-entered cmdlet for the Exchange Management Shell for me.... The Command should look something like this: get-messagetrackinglog -Start [Starting Date] -End [Ending Date] -MessageSubject [Bills] -sender: [Mail-Adressof our Company, which sends out Bills] | Select timestamp,EventID,recipients,messagesubject | Out-File [serverpath]\MailTrackingLogs\MailTrackingLog.csv And the command, that the Script creates, works.... if u copy and paste it manually into the Exchange Management Shell (EMS)... But I want the Script to automaticlly copy the Command into the EMS, but that doesnt work ... I was thinking about the cmdlet call shell ("powershell [Path of the PS-Script]") but that one only executes PowerShell Scripts and doesnt execute the get-messagetrackinglog cmdlet. Any idea´s on how I can rid of this Problem in my VBA Script? Now if anyone of you guys is into that kind of Stuff, pls help me Im Dying.... thx Greetings, Iqu
-
recently i programmed something in VBA ( i am just starting to learn how to code) and i ran my code on two computers with the same version of windows however the results where not what i was expecting. Computer 1: Processor: core i5 2 cores 4 logical processors base clock 2.30GHz Graphics: intel(530) RAM = 8 gb at 2133MHz Cpu usage = 65% Gpu usage= 10% For 100000 iterations it took 30.30s Computer 2: Processor: core i7 6cores 12 logical processors base clock 2.20GHz Graphics: intel(630) RAM = 16gb at 2667 MHz Cpu usage = 10% Gpu usage= 53% For 100000 iterations i stopped it at 7 min can anyone explain why computer 2 is way slower? this is the code
-
I'm writing a program using Python 3.7 that needs to export cells into an Excel document with part of the text in each cell underlined. To form the Excel document I'm currently using the openpyxl library, which, as far as I have been able to find, has the ability to style cell by cell, but not just portions of text inside the cells - I've also looked into using xlsxwriter, which has one picture that looks like it may have been able to format on the string level, but nothing in the documentation. I'm open to using any export solution though. I've also tried using U+0332, combining low line, between characters but since I'm working partially with Chinese text it does not render correctly. Text is scraped using Beautiful Soup 4 and portions that needed to be underlined could be enclosed in <u> tags if needed. If there is a macro or number format that I could use to remove the tags and replace them with an underline that would also work great for my use case. Thanks for your help in advance! Edit: So looking at the Excel object model it looks like the smallest object you can really work with is a range of one cell, not content inside the cell. Fairly certain that there isn't a Python library that can format pieces of the string, going to try to write something in VBA. Direction on that would also be appreciated.
-
I'm working on a program to automate some functionality of a research lab. One of the problems is that saving the file they want to use the sample ID as part of the name. Unfortunately I've run into some issues where " and / are used in the sample ID which as you know cannot be saved in Windows. The reason I'm changing variables from sampleID to correctedID is that I want to keep corrected id and my sample ID separate. I'm doing this because this program also automatically writes a several page report where the sample ID is called multiple times and needs the full sample ID. The bit of my code that I've inserted is this: Dim idArray As Variant Dim y As Integer correctedID = "" If InStr(sampleID, """") Then idArray = Split(sampleID, """") y = UBound(idArray) For x = 0 To y correctedID = correctedID & idArray(x) Next x End If If InStr(sampleID, "/") Then idArray = Split(sampleID, "/") y = UBound(idArray) For x = 0 To y correctedID = correctedID & idArray(x) Next x Else End If If correctedID = "" Then correctedID = sampleID End If Until I added this block of code there were no crashes whatsoever. With this block of code about 10% of the time excel will hard freeze with no error thrown and require me to kill it using command prompt. Thoughts? On the rare event that the code crashes BUT doesn't hard freeze excel it highlights a line near the bottom when I am saving the excel file. That line is: ThisWorkbook.SaveAs "T:\TCNA Laboratory US\PTCA\PTCA Data\20" & labArray(2) & " Automated\" & labArray(1) & labArray(2) & "\PTCA" & labArray(1) & _ labArray(2) & " C373-5 " & correctedID & ".xlsm" This is where I am calling the correctedID. I ran debugging statements to keep an eye on what value is stored in correctedID but it seems correct. If more information is required please let me know and I will provide it asap. Thanks!
- 5 replies
-
- visual basic
- vba
-
(and 3 more)
Tagged with:
-
Using the Yahoo finance API I am acquiring market data to fill out tables. The problem I am having is when I want to add a calculated field to both my form and my table the calculation only are applied to the first run through of the program and loops after do not get calculated again. I am new to access and to VBA so forgive the ignorance if this is just a simple fix. Option Compare Database Option Explicit Private Sub btnClearData_Click() CurrentDb.Execute "DELETE FROM HistQuotes", dbFailOnError Me.Requery End Sub Private Sub btnFetchData_Click() Const FilePathName As String = "D:\file.csv" Const tmpFilePathName As String = "D:\file1.csv" Const ForReading = 1, ForWriting = 2 Dim oStream As Object Dim WinHttpReq As Object Dim FSO As Object Dim filetxt, filetxt1 Dim myURL As String Dim InputString ' As String ' Dim FileToWrite As String ' Dim InputFile As String ' Dim OutPutFile As String ' Dim Ssql As String Dim ticker As String Dim StartDay As Integer, StartMonth As Integer, StartYear As Integer Dim EndDay As Integer, EndMonth As Integer, EndYear As Integer 'clear table CurrentDb.Execute "DELETE FROM HistQuotes", dbFailOnError Me.Requery Me.Repaint DoEvents 'import symbol data --------------------------------------------------- ticker = Me.cboTicker StartDay = Day(Me.dStartDate) StartMonth = Month(Me.dStartDate) - 1 StartYear = Year(Me.dStartDate) EndDay = Day(Me.dEndDate) EndMonth = Month(Me.dEndDate) - 1 EndYear = Year(Me.dEndDate) myURL = "http://real-chart.finance.yahoo.com/table.csv?s=" & ticker & "&d=" & EndMonth & "&e=" & EndDay & "&f=" & EndYear & "&g=d&a=" & StartMonth & "&b=" & StartDay & "&c=" & StartYear & "&ignore=.csv" ' Debug.Print myURL Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1") WinHttpReq.SetTimeouts 60000, 60000, 60000, 60000 WinHttpReq.Open "GET", myURL, False WinHttpReq.send myURL = WinHttpReq.responseBody ' Debug.Print WinHttpReq.Status If WinHttpReq.Status = 200 Then Set oStream = CreateObject("ADODB.Stream") oStream.Open oStream.Type = 1 oStream.Write WinHttpReq.responseBody oStream.SaveToFile FilePathName, 2 ' 1 = no overwrite, 2 = overwrite oStream.Close End If 'edit CSV header --------------------------------------------------------------- 'edit the header line. "Date", "Open" & "Close" are reserved words. Set FSO = CreateObject("Scripting.FileSystemObject") Set filetxt = FSO.OpenTextFile(FilePathName, ForReading) Set filetxt1 = FSO.OpenTextFile(tmpFilePathName, ForWriting, True) filetxt1.WriteLine ("Price_Date,Price_Open,High,Low,Price_Close,Volume,Adj_Close,Trial") InputString = filetxt.ReadLine Do While filetxt.AtEndOfStream <> True InputString = filetxt.ReadLine filetxt1.WriteLine InputString Loop filetxt.Close filetxt1.Close 'end edit CSV header --------------------------------------------------------------- Kill FilePathName 'rename the file Name tmpFilePathName As FilePathName DoEvents 'import the data to the table ------------------------------------------------ DoCmd.TransferText acImportDelim, "quoteimportspecification", "HistQuotes", FilePathName, True Me.Requery 'requery form DoEvents Set FSO = Nothing Set filetxt = Nothing Set WinHttpReq = Nothing MsgBox "Done" End Sub
-
I am in need of some questions answered for a very difficult access project I am taking on. I am converting a flat data system of my excel program into the relational system of the access app. I simply have 21 ETFs that are being recorded daily for Open High low Close. I will eventually figure out how to make calculated fields for Exponential moving averages, but that's a different issue. I have read and read and read "HOW TOs " but I have questions that I need to be answered in order to figure out if I am even doing the right thing, I am trying to make a sandbox where I can simply pick one of the ETFs in my list and run reports based on the dates and time intervals I enter. I have set tables with a general normalization understanding that hold the symbols in one table and the days in one table and all the data in between. I eventually want all the ETFs to update all data automatically with that specific end of day's data. Access app format has the lookup relations that are ridiculous to understand so I am at a road block currently. Do I have too flat of a model for access? Do I need professional help?
- 7 replies
-
- access
- newb status
-
(and 2 more)
Tagged with:
-
Hey guys, i got a little problem with a VBA command. I need certain cells to come up in the color.index=4 if the cell has the word "Voll" inside. The cells where the word can come up are in the range of (P5:S18). I already tried google but i couldn´t find a solution that i can adapt... Thanks for Tips!
-
I'm stuck in VBA, I'm looking for a language that's both marketable and usable in my current position. What I need to be able to do... 1. Automate windows terminal emulators(reflection IBM 3270 Terminals) 2. Automate MSOffice, excel/word/outlook/blah 3. Automate webpages using either IE or chrome. Currently I use IE, its just easier in VBA. 4. Has to be windows based...All end users use windows. Unfortunately, I don't have any database access right now. Or even database tools of any kind... It's a pain. But I'd like whatever I move to to allow me to connect to SQL server. I was going to start writing everything in vb.net, but I don't know if that's the best move for me. Even in VBA, i find myself writing a ton of "classes" because it makes all my work moving forward easier. I'm not scared of a learning curve, because I genuinely enjoy learning new things (As I sit here debugging code written in a language no longer relevant to a civilized society.). Until I decide a new place to rest my hat I've been spending a little bit of time every week familiarizing myself with the .net environment.