Free Microsoft Excel 2013 Quick Reference

How can I assign delimiters to an excel web query?

When performing a web query in excel, the data I need is all contained within
a single table. Consequently, when it's placed into my worksheet, it places
each line (each with differnt blocks of data separated by spaces) into a
single string. How can I get excel to recognize the spaces as delimiters and
place each item into separate cells?


Post your answer or comment

comments powered by Disqus
The data I need from a web page is contained within a single table.
Therefore, when I retrieve it via a web query, each line of data is placed in
excel as one long string. Instead, I'd like to have each element, separated
by spaces, placed in their own cells. Can this be done, and if so how?

I need to easily highlight corrections I'm making to an Excel spreadsheet.
I've been changing the font color for the changed text but this is tedious to
do on a case by case basis. I'd like to be able to change the automatic font
color so that everything I typed during the correction phase would be typed
in blue rather than black.

Any suggestions?

This is very simple, but I can't find a variable assignment function. How
can I assign a number value to a letter grade presented in an Excel table?

Thanks

I use a for loop to loop text data from a column and then assign to a string array.

However, the text data has background color which I need to get it too.

Code:
It was assigned before. e.g. 'A' with red color cell

How can I assign color cell to an object array?

hi,

I'm working on the excel automation in which i need to write a script
that'll aceept three command line arguments and then place them under the
coloumns say NAME STATUS and TIME. The script will run multiple no. of times
in a day and everytime it will open the same excel and then put the data in
the respective columns without deleting the previous content.Also, depending
on the STATUS passed as the argument it should be able to change the color of
the row. eg:- if status is passed as CRITICAL then the particular row should
become RED.The process should run at background.

I'm able to open the file and write data for the first time but when excute
the script for the second time it gives me message " cannot access filename"
i.e. it does not allow me to append the values.

Secondly, how can i make it run at the background?

One more thing that I noticed and I was not able to understand is that when
I try to open my *.xls file it opens it but it writes in some book1.xls . Is
it a temporary file that the system opens or something else is happening?
here is my code:-

void CExcelFormatDlg::OnOK()
{
_Application app;
Workbooks books;
_Workbook book;

Worksheets sheets;
_Worksheet sheet;
Range range;
Font font;
Range cols;
LPDISPATCH lpDisp; // Often reused variable.

// Commonly used OLE variants.

COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

// Start Excel and get Application object.

if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't start Excel and get Application object.");
return;
}

lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
ASSERT(lpDisp);
books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
// to the books object.

// Open a new workbook and attach that IDispatch pointer to the
// Workbook object

lpDisp = books.Add( covOptional );
ASSERT(lpDisp);
book.AttachDispatch( lpDisp );

lpDisp = books.Open("C:vivek.xls",
covOptional, covFalse, covTrue, covOptional,
covOptional, covTrue, covOptional, covOptional,
covTrue, covOptional, covOptional, covOptional,
covOptional, covOptional);
// Get the Sheets collection and attach the IDispatch pointer to your
// sheets object.

lpDisp = book.GetSheets();
ASSERT(lpDisp);
sheets.AttachDispatch(lpDisp);

// Get sheet #1 and attach the IDispatch pointer to your sheet
// object.
lpDisp = sheets.GetItem( COleVariant((short)(1)) );
//GetItem(const VARIANT &index)
ASSERT(lpDisp);
sheet.AttachDispatch(lpDisp);

// Fill range A1 with "1/25/98", the settlement date.
lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue2(COleVariant("change")); // Excel 2002 and Excel 2003

// checking for the particular cell if it's empty

VARIANT value1;
char str[]="A";
char strf[]="";
int i = 1;
char str1[10];
_itoa(i,str1,10);
strcpy(strf,str);
strcat(strf,str1);
range = sheet.GetRange(COleVariant(strf),COleVariant(strf) );
value1 = range.GetValue2();

while(!value1.vt == VT_EMPTY)
{
i++;
_itoa(i,str1,10);
strcpy(strf,str);
strcat(strf,str1);
range = sheet.GetRange(COleVariant(strf),COleVariant(strf) );
value1 = range.GetValue2();

}

range.SetValue2(COleVariant("Country"));
book.Save();
{
COleSafeArray saRet;

saRet.Detach();
}
app.SetVisible(TRUE);
app.SetUserControl(TRUE);

Please help me out here as I don't have much time in my hand.

Regards,
vivek

how can i replace the asterisk in an excel sheet?????

when i did find and replace all the cells gone blank....

i have names like.....

Ross**
Praveen*

and i am unable to remove asterisks...

i want

Ross
Praveen

How can I export a document from Excel to yahoo senders

How can I export address data from Excel (or MS Word datasource) to my Palm
Pilot software? Thanks for any tips.

How can I delete redundant records in Excel without then leaving behind what
will become a unique entry? I want all records that have a duplicate to be
completely deleted from my list, no original from the duplicate left behind.
Thanks!!

After scanning a barcode in excel, how can I automatically move to the next
cell without pressing the tab or enter key? Will be scanning multiple items
from a distance of serveral feet from the keyboard.

how can i spreed the range of excel sheet form 65000 of row to for example
123 000 rows

How can I hide a column in Excel that others I send the sheet or workbook to
cannot unhide, but can still add/modify other information in a sheet or
workbook?

=+Cost!D31/Price!$O$2 this formula is being used to link two sheets of
pricing together. I need to make an adjustment to this formula to times it by
1.05. How can I add this to the formula to adjust everything with
multiplying by 1.05. Thanks in advance for your help

I have the below block of code that adds a simple header row:
-------
Public Sub add_headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1:BD1").Select
Selection.NumberFormat = "@"

Count = 0

For Each cell In Selection
Count = Count + 1
cell.Value = "D" & Count
Next cell

End Sub
-------

How can I rewrite this using an Array to indicate my range instead of
selecting it? The Array would have 56 elements.

Thanks
Fred

I wish to create a macro which would activate a text box on event. I have a range with 12 months. Under this range I created 12 text boxes with comments for each month (one on top of another). Click on a month range should bring forward a text box with comments for a respective month.

I recorded a simple macro which brings a text box forward. How can I assign it to an event (run macro when month cell is selected)?

Sub SelectJune()

ActiveSheet.Shapes.Range(Array("Text Box 2", "Line 10")).Select
Selection.ShapeRange.ZOrder msoBringToFront
ActiveSheet.Shapes.Range(Array("Line 6", "Line 7", "Line 9")).Select
Selection.ShapeRange.ZOrder msoSendToBack
Range("H1").Select
End Sub

I attach a file with example. Thank you in advance!

how can i make macros to work when in tools, costumize and option are not
highlighted? please help me with macros

If I use the 'On Error Goto' to send a report by email how can I
include the line code that caused the error?

And how can I still go to the vba-code-line to debug it?

It seems Err. is missing something like Err.Line or Err.Code.....

i'm writing to an excel file from an excel file...and i might (on a network) have more than one person writing at a given time...

here's the code i'm using (works well for one user):

Dim wb As Workbook
Set wb = Application.Workbooks.Open("3-serverdatadatabaseschedule.xls")

?will i have any problems with multiple users...?

thank you!

I have code contained within the tabs, On Activation
How can I prevent access to this code by other users.

Also how can I prevent access to the normal code ie stop access through the
Tools Macro selection.

Regards

Lee

columns reads as 1 2 3 4 as do the rows, how can i get columns to read a b c
etc making financial spreadsheet building easier?

How can I remove the accents in excel 2003?

How can I use engineering functions in Excel 2003 in my package (excel 2003)
they are absent.

How can I alternate row colors in Excel and keep the effect after changing
the sort order???

Hello,

Can anyone tell me plz how to Bypass username/password prompt when executing an excel web query. I want other users to be able to run that from excel and I don't want to provide them my password or username. Thank you


No luck finding an answer? You could always try Google.