I have a VBScript that pulls SNMP data from some devices. It uses two external EXE's called "SNMPGET.EXE" & "SNMPWALK.EXE". When the script runs, it pulls specific information from our SNMP devices (in this case, I am pulling temperature values from Netboz Devices). The script looks like this :
View 2 RepliesI'm having some trouble with a little VBScript I have put together to export data into a CSV file.
The VBScript goes through each individual Row in excel and basically exports it to a CSV file in the format I need.
Code:
Dim JobHeader As JobHeader
Dim JobDetails As JobDetails
Dim FileNameStr As String
Dim RowStart As Integer
Dim MsgResponse As Integer
Option Explicit
[Code]....
CloseFile:
Close #1
End Sub
Sub ProcessJobs()
JobDetails.JDShipName = Cells(RowStart, 3).Value 'Col 3
JobDetails.JDStreet = Cells(RowStart, 5).Value 'Col 5
JobDetails.JDBlock = Cells(RowStart, 6).Value 'Col 6
[Code]...
This is working perfectly however there can be multiple lines of different product for the same Order Number and I need to consolidate this into one single line where the JDItemDescription, JDQuantity and JDWeight are updated with the combined data.
I have attached a copy of the Spreadsheet so hopefully you can understand what I am after. [URL]...
i have created a Vlookup that pulls data from another work sheet, it all works fine apart from this one thing....
On a work sheet i have a lot of hyperlinks that work fine, but as soon as they get pulled over using a Vlookup, the name of the hyperlink comes accross for example "link 1" but you can not click on it to take you to the link like you can on the original one.
I'm creating a master spreadsheet and I want it to pull live data from other spreadsheets depending on a variable.
I've a cell that has a variable date. I've files saved that have this variable attached to it in the name format: 'filename variable'.
I want to pull data, hopefully live, from workbooks based on this variable.
For example, I have files: generic 01212014, generic 01222014, generic 01232014.
The date is stored in the master workbook say in A1 that I can manipulate by entering a different date to pull data from a different workbook.
='[generic 01212014.xlsm]Database'!$L$3
='[generic 01222014.xlsm]Database'!$L$3
='[generic 01232014.xlsm]Database'!$L$3
There has to be away to make the bolded portions of the file name variable, right?
I am having problems with the function vlookup. I attached an excel file of my problem. It seems to pull the correct lookup, then it pulls the wrong data until a correct one occurs. My excel attachment should make more since of my problem. It will also pull data that is invalid. For example, the name "S5" is not a in my original data so it should not be able to find that?? Sorry, this is hard to explain - let me know if the file is not clear enough.
View 3 Replies View RelatedI need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
View 8 Replies View Relatedcreate a formula or script that will convert "Decimal Degrees" to "Degrees and Decimal Minutes"?
Here is a formula that converts Decimal Degrees to Degrees-Minutes-Seconds. However, I need it converted to Decimal Degrees.
Assuming your number is A1, try the following
=INT(A1)&CHAR(186)&" "&INT((A1-INT(A1))*60)&CHAR(145)&" "&ROUND((((+A1-INT(A1))*60)-INT((+A1-INT(A1))*60)),2)*60&CHAR(145)
Latitude and Longitude Formats
Format Example Usage Details
Decimal Degrees 35.73972222 GIS Applications The degrees are listed as the integer portion of the number. Any minutes and seconds are converted to their decimal equivalent and added to the degrees. Since there are 60 minutes in a degree, you divide minutes by 60. Since there are 60 seconds in a minute or 3600 seconds in a degree, you divide seconds by 3600. No hemisphere is listed. The northern and eastern hemispheres are considered positive numbers and the southern and western hemispheres are considered negative numbers.
Degrees Minutes Seconds 35° 44' 23" N The Layperson The degrees are listed as the integer portion of the number. Any minutes and seconds are converted to their decimal equivalent and added to the degrees. Since there are 60 minutes in a degree, you divide minutes by 60. Since there are 60 seconds in a minute or 3600 seconds in a degree, you divide seconds by 3600. Usually the hemisphere is listed after the number.
Degrees and Decimal Minutes 35 44.38333 N GPS Devices The degrees are typically listed by themselves. The minutes and seconds are listed in decimal format with the minutes being the integer portion and the seconds being the fractional portion of the number. Any seconds are divided by 60 to be added to the minutes in this format. Usually the hemisphere is listed after the number.
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
I have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
View 2 Replies View RelatedI work in IT Service company and we need to calculate our workload based on a number of devices in our scope.
Say, somebody wants to add 300 Windows Servers to our service desk. We know that each Win Server would need 1 Person on duty to manage. So ideally we would just multiply 1*300 and get the amount of people we need.
Problem is that because the service is quite similar for each of these servers, we dont really need a dedicated person for each device so I want to make every additional device consume 20% less workload than the previous one.
In our case it would be:
1 Server = 1 Person
2 Servers = 1.8 People
3 Servers ~ 2.5 People
Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup.
View 11 Replies View RelatedI have a spreadsheet of device names and IP addresses. I want to be able to click on an IP address and have it automatically invoke A SSH program ( putty.exe ) to automatically login to the device.
View 1 Replies View RelatedAll I am trying to do, is read an Excel file looking for the next cell to update, then update it and ultimately save it.
I cannot get it to work - all the checking says it should and yet it doesn't. I have included my simple code but not being a programmer, I just need this to work. The whole Excel VBSCRIPT is a mystery to me as to when methods etc can be used.
here comes the code
Set objXL = CreateObject("excel.application")
Set wbDest = objXL.Workbooks.Open(pathName & xlFile)
Set wksDest = wbDest.Worksheets("SpeedLog")
xlFindColCount = 1
Do Until wksDest.Cells(1, xlFindColCount).value = ""
xlFindColCount = xlFindColCount + 1
[code].....
Basically I want to be able to get the names of all sheets in a workbook. I know how to get Excel to print the name of the sheet in a cell once the file is saved, and I know how to hide sheets to make sure that all sheets are referenced in a range. If the name of the worksheet is in the same cell across all sheets (except the ones I don't want included on the totals page), is there a way to call that range and have Excel print off all the worksheet names in different rows or columns via a fill function?
Difficulties: the user may add/delete, rename, and have any number of, worksheets in the workbook. Because these variables are unpredictable, I am having difficulty figuring out the way to do this. Also, the formula should be able to ignore the sheets which have no value in the expected cell.
I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.
What I believe is the pertinent part of the code that needs adjusting is:
"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "
How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies
I have been working on the following worksheet, which has A3-Z27 cells right now and will grow/shrink with time. The spreadsheet shown below is just a portion of the entire worksheet. My vbscript is below the worksheet. I have two worksheets PL Dbase and Waiting List. I am copying columns B thru G from Waiting List if Column J is = to "Yes". To the next available row in worksheet PL Dbase columns B thru G...These columns have the same headings on both worksheets. I am getting the following error, when I click the button I assigned the macro to: Run-Time Error 1004 Copy Method of Range Class Failed.
Waiting List .....
I have a vbscript that creates a workbook in an existing spreadsheet. I would like to have the script create the spreadsheet if it does not exist. That part is easy enough. The part I don't know how to do is to have the macro that has to run on the sheet created by the script.
View 9 Replies View RelatedI am trying to open an Excel Workbook which in turn will automatically run a macro on loading which is a Userform. The Userform will contain 3 boxes, just some simple text boxes.
I don't want the Excel Workbook to show in the background, so I have opted to go for a VBScript to call the Excel Application and open the spreadsheet...
I have a current .vbs script which is as follows...
Code:
Option Explicit
On Error Resume Next
ExcelMacroExample
[Code]....
This works fine, there is no workbook showing and the userform opens as expected. The problem is, I lose the functionality to tab between the boxes. When opening the Userform directly in Excel,
I've written a little VBScript that generates several hundred Excel 2013 worksheets. In order to protect the users from themselves I protected certain cells. Everything works as expected, except when I discovered that the password I used to protect the worksheet doesn't work to unprotect it. If it matters, I'm not trying to unprotect programmatically, just opening Excel and going that route.
Here's a snippet of my code:
objWS.protect Password="abc123"
I have a two identical worksheets in the same workbook with two tabs (Search, Database). The Search worksheet contains a search engine at the top which searches by column, and pulls rows from the Database tab, which match the search queries. However, the rows that are pulled up in the Search tab cannot be edited as they are all MATCH formulas. Only the rows in the Database worksheet can be edited.
This can be quite problematic if I am searching a row to edit it. Although the search engine is convenient enough and faster than filtering (as I have over 20 columns), when the row comes up, I cannot edit it. I have to go to the Database sheet and filter through until I find that row, and THEN edit it. What I require is that when I search for something and the matching rows get pulled up, I want to be able to click a button which will take me to those rows in the Database sheet.
see the below link...
http://www.mrexcel.com/forum/showthread.php?t=350701. So far this is the closest I got, but it only pulls the values for 01 Nov. =NDEX(NS_Tally!W1082:W1360,MATCH($J$32,NS_Tally!V1082:V1360,0))
I've got a working macro that pulls a Userform. Everything works correctly but I'd like to have the form displayed on the far right instead of center screen. I've tried all the options on the form properties list to no avail.
Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMaximized
Worksheets(1).Select
Range("A1").Select
UserForm_1.Show
End Sub
I am trying to create a spreadsheet for all the different Myers Briggs personality types. I want to pull down on a menu say ISFJ and in the next column will produce how to communicate to a person who has an ISFJ personality.
View 9 Replies View RelatedHow would the formula look that pulls/puts the right number in E19 and F19 respectively, taking input from the list above?
View 2 Replies View RelatedI am trying to put together multiple userforms that perform separate tasks. I have been using code found on this forum, and it's helped out a lot, but i'm still having trouble on one of the userforms. the one that i'm having the biggest trouble with called frmEditData. What it is designed to do is pull data from different fields and display them in the textboxes on the different sheets. then, the user has the option of updating those tabs and saving the changes. For the life of me, i cannot get the data to change. it always reverts back to the original information in the cells.
i have to say again that I don't really know how to read the code too well just yet, and i've been copying code that appears to work on other sheets, but it might not necessarily work here. I have attached a rough copy of what i've been working on.
Is it possible to get Vlookup to pull the format of text across to the new cell?
For example, if the source data is bold, can you get it to show up as bold in the new cell? Or worse, if there are multiple formats in the original cell (like some text is bold, some is red font etc.) can that be pulled across through Vlookup?
I am trying to create a drop down box that pulls its selection from another sheet in the workbook and I'm not sure how to go about doing it. I figure VBA must have some way of doing it but I am not sure how the coding would go. I've attached a very basic example of what I am trying to accomplish.
Drop Down Box Ex..xlsm
I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.
At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.
However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.
Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.
So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.
I have a script that pulls my stock prices every minute and update an excel page I've created...basically an excel stock portfolio...the problem I'm running into is when a stock is unchanged...instead of being $0.00 it returns "unch"...as you can imagine this throws my calculations into chaos...I have the following formula but it doesn't seem to be working =IF(J18<0,R18*J18,(IF(J18>0,R18*J18,0)))
Basically J18 is the returned change in stock price...R18 is the number of shares held...how can I get it to view "unch" as $0.00?
New user with basic knowledge faced with a multi-part challenge.
I have a list of 10 items (multiple words) in a drop down
Based on what is selected in the drop-down, I am trying to figure out the best way for it to pull the corresponding text paragraph. This would then be concatenated with a numerical value inputed b the user in another cell. The final output would be displayed in a final cell.
I have figured out how to concatenate between one cell and the other cell with the paragraph but I am at a loss for how to make it more dynamic in the sense that the drop down selection drives the paragraph being selected and then taking the value in cell A1 to concatenate within that paragraph and outputting the final text to be copied out from excel.
I tried looking at posts for dependent data verification and I felt like I was on the right track but hit a wall.
Excel 2003