How I can directly write info from an external application to an Excel sheet
Actually, I'm using a special dll collection to do it, but I would need to buy the 64 version soon. Problem is I use only one function of the entire collection so I want to look on how to create it my self. Basically, it took data from my charting software and write directly to my Excel Worksheet.Cell
I have the following code which edits an excel file and saves it in a different format, I think want the code to take this newly saved file and open it in an external application (Softplot) and then save it through this new application.
VB:
Sub FormatMacro1a(ws As Worksheet)
ws.Copy
Rows(1).Delete
Columns("D:E").Cut Destination:=Columns("J:K")
Columns("F:K").Cut Destination:=Columns("D:I")
Range("E1:E201").Value = "0"
[Code] .....
As it stands I have the file saving in a new format and I can open a specific file in softplot through VBA however where I am getting stuck is opening my newly saved file and then saving it.
I have tried the following :
VB:
Path = "C:Program FilesSoftPlot-8softplot.exe"
File = "ActiveWorkbook"
I have a worksheet that contains two basic columns of data, A and B. What I would like to do is based on the value in column A, I would like to copy the contents of column B to a textfile (preserving the basic line structure and hopefully without any extra characters like quotation marks attached). So for all the values in column A = 'Account', Id like to take all the corresponding values in Column E (for example) and save them to the same file, preferably where the filename itself as 'Account.xyz'. And I would like to do this for all unique values in Column A, that is do it for 'Account' and 'AccountPrivilege' etc. Note Column A is essentially presorted alphabetically already. There are actually 1000s of rows with hundreds of unique A values so Id like to find a way to automate this process.
A
B
1
Account
Company Information
2
Account
Company
[Code] ........
I have dates imported from an external application in the format:
71205
for december 5th 2007, for example
I need to send emails and have the "Direct Replies To" field updated to a different address.
This is my code. I've remarked what I tried, which is NOT working.
VB:
Dim OutApp As Object
Dim OutMail As Object
Dim EmailBody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
[Code] .....
Excel 2003
On one of our computers at work, Excel is acting especially strange. The conditional formatting in Excel on one of our computers is not working. In addition, we cannot fill a cell with any color or change the color of the writing. We can click a color for the letters and type it in, but immediately after another cell is highlighted, the writing goes black again. When we try to highlight multiple cells with the cursor, the cells are black instead of the normal color. This happens to not just spreadsheets that are new, but existing spreadsheets on our shared drive from only this one computer as well. No other computers are affected this way. All other computers show the spreadsheets fine and do not have any functionality issues.
I Want users to be able to copy a reference number from a an external program into excel, this works well for single cells but the cell I want them to be able to paste the information into is a merged cell and keep getting the "Data on the clipboard is not the same size and shape blah blah error". What I can do to get this information to paste? Is there some VBA that can kinda trick the cell to believe its single or when pasting it unmerges and then remerges again?
View 1 Replies View RelatedI have an Excel 2003 list with four columns as shown below:
Zipcode
CRRT
Count
Bundles
85710
C004
693
14
85710
C005
867
18
85710
C006
1021
21
I want to "expand" this list to use in a Mail Merge program to produce tags for our direct mailing. Each bundle contains 50 letters, so in the first line on the above spreadsheet, there will be 13 bundles of 50 and 1 of 43. Currently, I can cut and paste to create the following table, but there has to be an easier way...
Zipcode
CRRT
Count
bcount
Bundle
ibundle
85710
C004
693
50
1
14
[Code] ........
So as I pull this into a mail merge I will get a tag that shows the zipcode, the crrt, the total pieces for that crrt, then number of the bundle for example " 1 of 14" and the quantity in that bundle, for example "50 of 693", then the next bundle tag will print, 3 to a page. I know that here has to be an easier way either in the mail merging process itself or with EXCEL? Am even open to build these with ACCESS to create the tags. The beginning database contains anywhere from 350 to 800 lines of original data, so as you can see the cut and paste is VERY time consuming.
The spread sheet I have written reaches a point, where I want the "ENTER key depress" to jump to a cell to the right instead of the cells in the same column lower in the sheet, is there a way to do this?
View 9 Replies View RelatedThe goal is to have a data validation drop down list that adjusts the File.xls name portion of the external cell reference (example given below) and return the result of the cell I am searching in the new file after updating my links.
I am trying to set up an external cell refence within my workbook for example:
='[File.xls]Tab'!$C$9
When I have tried setting the equation to pull from the drop down list in cel A1 for example:
="'["&A1&"]Tab'!$C$9"
It only shows the new file path name and not the value in the new sheet--even after updating links.
I am also attempting to do this using the my network places file path instead of the letter drive in my computer.
Is there a way to incorporate these 3 boxes into one? So if I enter a figure into any cell, it will calulate it for the other two? Without overwriting the existing formula. I have attached the problem.
problem.xlsx
I am attempting to setup a spreadsheet that summarizes data from a set of other workbooks. The woorkbooks the data comes from is different for each year. However, I would like to have just one summary that can be changed based on the year entered.
In B1 the user enters the year. Say 2012
In the bulk of the spread sheet below the cells refrence
='C:...["&B1&" Pt. Visit Stats.xlsx]Summary'!$B$6
As I am sure many of you can guess I am getting a #REF! error. I just do not see why it shouldn't work.
I do not want to use the indirect function, because the refering workbooks would need to be open to draw data. I am guessing that I will probably need to research the Harland Grove Pull VBA work or more likely include a copy of Morfunc and then use the indirect function. But, I though I would ask here first just in case I am missing something relatively simple.
I am using Excel 2010. I am trying to make a spreadsheet where I can type in a date (02-19-2014), and pull in box scores from a basketball website. I have everything set up so that the date is parsed into (3) cells (02 19 and 2014) so that it can easily fit into the URL:
[URL]
As you can see, the month value in the URL is "02" the day value is "19" and the year value is "2014". What I want to be able to do is type in any date I want and return the box scores from that day in a new tab. I have everything done so that the new tab is automatically created and named, so my only issue is that I can't figure out how to input the day, month and year values automatically into the code for the Get External Data pull:
Code:
'Import from www.basketball-reference.com
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.basketball-reference.com/friv/dailyleaders.cgi?month=02&day=19&year=2014" _
, Destination:=Range("$A$1"))
.Name = "dailyleaders.cgi?month=02&day=19&year=2014"
I have a template with formulas calculating a default value, but still allowing the user to override the cells with direct input.
I want to use conditional formatting to highlight any cells that have been overwritten, but can't find a way for Excel to differentiate between a cell with a formula or an inputted constant.
I realize there is a VBA "isFormula" function, but I don't want to have to use VBA for this.
I have been working with Excel, access and vba from 4 years. But never worked with PDF files along with vba.
Now I have a requirement to write data from excel to pdf. I know about using Acrobat distiller to create Post Script file and then converting into pdf files.
But I wanted to know if there is any way to write data directly into PDF from excel using VBA.
I have 6 columns. The 5th and 6th columns are the most important. The 5th contains a figure and the 6th contains a type.
Bascially I am trying to do a basic chart showing the following 2 things. A. Do a percentage chart of column 6 (service) but not of percentage of hoem many lines the product is in. Not based on percentage of how many times Apples are shown compared to other products. I need it to add up everything, then do a percentage of total amounts of oranges and total of for Apples from the figures on column 5. Also, if possible, the pie chart will even show teh actual total figure per product in each (or next to) each pie chart slice.
know the easiest way to do this (step by step) in Excel 2010 and then I can perhaps write a macro for the future?
Background: The user makes a selection from a drop down box on the main sheet (sheet5, G12). The drop-down box is linked to *Sheet31.Pax_Nav*. If the drop-down box's linked cell value is less than 5, then do nothing (manual input required). If it is greater than 5, then the vlookup matches that number to a person in the database and returns their weight. The code will pull the required person's weight but then Excel will hang and freeze.
Private Sub Worksheet_Calculate()
On Error Resume Next
If Sheet31.Range("Pax_Nav") > 5 Then
Sheet5.Range("G12").Value = Application.WorksheetFunction.VLookup(Sheet31.Range("Pax_Nav").Value, Sheet31.Range("H17:L48"), 5, False)
Else
End If
End Sub
Currently I work on a file which will be more like a form which will be filled by user and after he or she hits the button, the filled data will be written or sent to another Excel file or even Sheet in the same workbook which will store the filled data in database like form. The same as some web form which will post the data to database.
In other words, the workbook contains to sheets, one with forms to be filled in (cells) and one with specified columns. After filling in the cells on the first sheet and hitting the button, it will trigger the macro which will send data to second sheet and post data from each cell to appropriate column.
I need in XL for a macro to run, open my word file, copy the entire thing and then paste it back in XL.
So leaving it general, Word.doc and Excel.xlsx
It's all because my company's POS system will only export to Word but everything we do is in XL.
a formula to direct me to a certain tab.
Example:
I have a cell with a number, say 312260 and a tab with the same number as the name, I need a formula to send me to that tab.
I have a problem, basically im writing in the cell but it keep going along, i want it so when i press enter it creates a new line in the cell, so i can have two lines of text in one cell. do i have to do something in the text settings?
View 2 Replies View RelatedI have a reference written as a string in cell "AS4", the string is results!A4
I want to write that same reference in cell "as5". I have written the following
[Code] .....
However, cell "as5" returns the string as =results!'A4', with single quotes around A4
Therefore the reference isn't working.
I'm working on creating an array of letters. I'm then writing that array into a cell. The following code is for writing the letters "ABC" into a cell with an array.
View 4 Replies View RelatedUntil now, I have been using the terrifically inefficient way of writing in a formula in a cell and copy pasting the value in my VBA
Easy example (concatenating):
Sheets(Sheet2).Select
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=""Data collected on ""&TEXT(NOW(),""dd/mm/yyyy"")&"" at ""&TEXT(NOW(),""hh:mm"")"
Range("D1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
More complex example (with IF statement):
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-3],13)=""No list found"",""Data requested for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& "" out of range"",""Data downloaded for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& """")"Range("D2").Select...............
I have code that opens a .pdf file and copies the content to an excel spreadsheet.
However, in order to loop through multiple pdf's, I need to be able to close the Adobe Reader application before I open each new pdf file.
With excel being the active application, any suitable code to activate and close Adobe Reader?
I have a drawing index in excel sheet as shown below "there are thousands rows but here is only an example":
FILEID
TITLE
brd213962-1-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-2-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-3-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-4-1.pdf
SPECIAL PIPE SUPPORT DRAWING
brd213962-5-1.pdf
SPECIAL PIPE SUPPORT DRAWING
And in the same folder I have all the drawings in PDF format and all the drawing are exactly named as listed under FILEID column.
I just want to click the drawing number in the excel sheet and get the drawing opened automatically.
Currently what I am doing is searching the index for a particular drawing and then searching the folder for that drawing to open.
Is there a way to connect direct to the newsgroup below via OE6:
microsoft.public.excel.worksheetfunctions
It doesn't appear in the newsgroups' list ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
I have a cell in which I use the following formula:
View 13 Replies View RelatedProcess is that I read in lines from a text file and then based on a filter I then write some those values to a sheet. One of the values I transfer is always 5 characters with a trailing space. The first four may be all numbers (e.g. "0013 ") or a mix (e.g. "013G "). When I write these to the spreadsheet the ones without letters always end up as a number (e.g. "0013 " become 13). How do I force the cell to show all values as text in the cell just like I read it in. In fact, I'd prefer stripping out the trailing space but it's not critical. That I can do (TRIM).
View 4 Replies View RelatedI have a combobox that the user selects a value from. Depending on what value is selected i want to write three different numbers to three different cells. I pretty much need to know how you get VBA to put a number in a cell. The numbers are nowhere on this sheet. Just in the code.
Case "1"
Sheet1.Range("M31").Activate
ActiveCell.Value = "1"
Sheet1.Range("O31").Activate
ActiveCell.Value = 0
Sheet1.Range("Q31").Activate
ActiveCell.Value = 0
Case "2"
Sheet1.Range("M31").Activate
ActiveCell.Value = 2
Sheet1.Range("O31").Activate
ActiveCell.Value = 0