Free Microsoft Excel 2013 Quick Reference

Works 7.0 database files to Excel 2007

Can I transfer my database files in Microsoft Works (ver 7.0) to Excel?


Sometimes we convert database file to Excel file but the structure is not what we want. See db.xls, B2:D20. I want to rearrange the cell range B2:D20 to the format F2:R6. Put the corresponding numbers or letter (D3:D20) in the range G3:R6. If there is no sale level in certain month, put 0.

Is there a formula that can be put in the cell range G3:R6 to achieve this?



I don't quit know how to explain this but I will give it a try :-)

One of our users want to import a .CSV file to Excel 2007. During the import
wizard it's possible to change how Excel handles "." and "," (Without the
quotes) in numbers when importing. It should be possible to swap them around,
it worked in Excel 2003 but not in 2007.

Any idea?


Hello Everybody,

Purchase new computer with Vista,
Vista nixes installation of my old Lotus 1-2-3 program (registry
Purchase Excel 2007,
Excel nixes understanding of Lotus 1-2-3 files,
Google-search internet and find, which succeeds in
converting a few Lotus files to Excel but gives up repeatedly on
Google-search again and discover at a free download of
"Excel File Conversion Wizard" (yes, also from Lotus 1-2-3 to Excel)
with not a word of usage instructions,
Download it to where?,
Finally find it on Desktop as fileconv.xla,
Try Excel now,
Just as dumb as before,
Then try clicking on fileconv.xla itself,
Excel again plays dumb BUT also sends a note analyzing why it is dumb:
"Errors were detected in file fileconv.xla" and "This workbook has
lost its VBA project, ActiveX controls and any other programmability-
related features".

Obviously something more than just download should be done, but what?
Curtain, end of the game for this scant brain. Anybody knows? Anybody
wants to restore happiness to the world?

Hugs, Clas

I am trying to open Quattro Pro 7.0 files (.wb3) in Excel 2003. It says in
their documents about supported file formats that this file format is
supported for opening in Excel 2003, with a converter installed. I've
installed the entire Excel 2003 software, along with all service packs and
the Office 2003 Conversion Pack. I am still unable to open Quattro Pro 7.0
files in Excel 2003. There are downloadable converters for Excel 2000 and
97, and those work fine for those version. But, there is nothing for Excel
2003 and information is scarce.

I have been importing text file into Excel 2007 by using Open command. This
works fine, but when I try to copy the sheet to any other workbook, I am
getting a message to say there are too many rows and columns to carry out the
operation. The text file has several hundred rows, but when opened in Excel,
the workbook has 1048576 rows instead of the standard 65536. This means
that it will not copy to a workbook with the standard number. Is there a way
round this? I do realise I can copy and paste, but would like to know the
answer to the copy sheet question if possible.

Greetings Excel Gurus,

I have an old calculator spreadsheet for work that used Microsoft Excel 5.0 Dialog Sheets. This feature was replaced by UserForms in Excel 97. The spreadsheet no longer runs in Excel 2007.
Microsoft has a dialog converter for Excel 97 users. We use 2007, so it says that I must have Excel 97 installed when I try to run it.

Perhaps my searching abilities are lacking, but I have not found a way to convert MS XL 5.0 dialog sheets for use in Excel 2007.
Does anyone here know how to accomplish this?



Very new to excel 2007. I have the following piece of code that allows me to import and array the data in excel 2003 but does not work for excel 2007. Does anybody have any ideas?

Application.StatusBar =
"Importing 0367 Era.Dat File..."
        Workbooks.OpenText Filename:=txtDirectory, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(10 _
        , 1), Array(37, 1), Array(45, 1), Array(64, 1), Array(85, 1), Array(97, 1), Array(101, 1)), _

Thank you in advance


Hi all!

I'm new to Excel 2007 and I'm trying to get something to work. I'll explain:

The current template when opened gets it's data from a SQL table. Here's what I would like to do:

1. Once the data is downloaded into the file (automatically) I would need it to automatically apply 2 formulas (i.e. A1 + 50, B2 + C2 etc.). But I wouldn't know how many rows there will be. Sometimes there will be 5 rows, other times there may be 50 rows.

I don't want the end user to do anything (such as copy the formula from the first row down to however many rows there are). All they would do is open the template and the refreshed data would be populated and formula columns as well.

I created a macro that would run when the end user clicks a button. So far I got the formula to work for one row:

Worksheets("Sheet1").Range("N2").Value = Application.WorksheetFunction.RoundUp((Range("B2").Value / Range("C2").Value), 0)

How do I get this to work for all the rows, note I don't know how many rows there are as this changes.

Is this possible? Thank you in advance!

I'm trying to open a large Excel 2003 (.xls file) in Excel 2007. The file is about 172,000Kb and contains lots of calculations, formatting and macros.

The file opens for Excel 2003 users with no issues.

For Excel 2007 users, the file downloads OK yet Excel stops calculating when the calculation reaches 24% (on the progress bar) and displays the following message "MS Excel has stopped working."

I have tried calculations with both marcos enabled and disabled. Any explanation or suggested resolution for this?

I have 2 large spreadsheets which were created in Excel 2003. One spreadsheet has formulas in numerous cells which pull in data from the other spreadsheet. I have now upgraded to Excel 2007 and saved the files as xlsx. The formulas pulling in data from one spreadsheet to the other don't always work. Settng to Auto Calc doesn't work. Any idea why or how to fix?

I need to convert several hundred text files to excel, format the text files, and then save them as excel.
What I have is a folder with all of the text files and an excel sheet with the file name of each of the text file in column 1, the folder path in column 2 and .txt in column 3. I then try to run this macro to open each text file, edit it, save it as an excel and then repeat. When I do though I get an error 1004 saying "excel cannot find the text file to refresh this external data range"
When I just run the formatting part, plugging in the file names, it works fine for one file. And the other part of the code works fine when the formatting section is not in place. I just cant get them both to work at the same time.
Any help would be awesome! I havent used VBA very much and don't really know what to do to fix the problem.

    Dim curbook As Workbook 
     ' curbook is the current workbook
    Dim x As String 
     ' x is going to be the file path
    Dim y As String 
     ' y is the name of the file
    Dim i As Long 
     ' i is the counter
    Dim lr As Long 
     ' I think this stands for last row
    With Application 
        .DisplayAlerts = False 
        .ScreenUpdating = False 
    End With 
    Set curbook = ActiveWorkbook 
    lr = Cells(Rows.Count, 1).End(xlUp).Row 
    For i = lr To 2 Step -1 
        y = Range("B" & i).Text & Range("A" & i).Text 
        x = y & Range("C" & i).Text 
         ' This is the macro to open the file
        Workbooks.OpenText Filename:=x, Origin:= _ 
        437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _ 
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _ 
         ' This is how I want to edit the text files
        With ActiveSheet.QueryTables.Add(Connection:= _ 
            "TEXT;x", Destination:= _ 
            .Name = "y" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .TextFilePromptOnRefresh = False 
            .TextFilePlatform = 437 
            .TextFileStartRow = 3 
            .TextFileParseType = xlFixedWidth 
            .TextFileTextQualifier = xlTextQualifierDoubleQuote 
            .TextFileConsecutiveDelimiter = False 
            .TextFileTabDelimiter = True 
            .TextFileSemicolonDelimiter = False 
            .TextFileCommaDelimiter = False 
            .TextFileSpaceDelimiter = False 
            .TextFileColumnDataTypes = Array(1, 1, 1) 
            .TextFileFixedColumnWidths = Array(31, 47) 
            .TextFileTrailingMinusNumbers = True 
            .Refresh BackgroundQuery:=False 
        End With 
         'This next part closes the file
        ActiveWorkbook.SaveAs Filename:=y & ".xls", _ 
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
        ReadOnlyRecommended:=False, CreateBackup:=False 
         'This re-activates the workbook with list of files
    Next i 
    With Application 
        .DisplayAlerts = True 
        .ScreenUpdating = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

We have certain files with fairly simple financial figures with formulas such sumif, countif etc and with simple charts. These were created a during the last few years either with excel 2000 and 2003. Now when we upgraded to excel 2007 and open up these files, they work very slow. If any one has any ideas on any settings that we can change?


I'm new here. I've never needed help with excel before, but we just upgraded to Excel 2007 (winXP) and a lot of my formulas from my old workbooks are not working. Specifically, the vlookup formula for data that comes from a pivot table.

I'm wondering if this is a known bug, or if there is some change to the way this is done in excel 2007, but I've read all the help and searched on google and couldn't find anything to help me.

Here's the situation:

On worksheet 1 I have bank downloaded data, worksheet 2 I have credit card transaction data, worksheet 3 is pivot table from CC transaction data (to sort by batch number), along with a vlookup to tie that total batch amount to the bank dowload information on worksheet 1.

The vlookup is only working on about 70% of the lines for some reason, the rest are displayed as #N/A even though the data is all there. It's all formatted correctly, no text or anything, they are all number formatted exactly the same. I don't understand how it could work for some of it but not all of it?

I'm not sure how to post examples, but the data in Worksheet 1 is simple and I'm only using columns A & B in my table lookup for the vlookup, A being the common cell and B being a date. So, my vlookup formula is as follows on worksheet 3:

=VLOOKUP(G5,'Bank Activities'!$A$2:$B$99,2,FALSE)

70% of the time, it is returning the correct date/value. For the other 30%, it is returning #N/A even though that value G5 or whatever is definitely in the table that is referenced. And it seems random, I can't find any similarities between the ones that are coming back fine and the ones that aren't working. And I just pulled down the formula for the whole sheet so there's nothing different between any of the formulas or data.

I'm sorry that was lengthy and probably confusing, it's so hard to explain this in words. Is there a way to attach the file I'm working on?

My company has recently converted from Excel 2003 to Excel 2007, which is fine. I have continued to work as normal; however, I notice that saving files has become an issue, in fact, turned into a big problem. I get in a hurry and save it without regard to what version of Excel I'm saving the worksheet as. Now I've noticed multiple copies of some of the same speadsheets out there and I don't really know which copy is my latest and greatest. Also, spreadsheets that used to link to one another no longer do. I feel like this is my own doing for not being more judicious when it came to saving. Can anyone out there lend a hand? Thanks in advance.

I'm being asked to test 5% of the files in a hard drive given to us.
There are 1620 files, so I need to test 81 randomnly choosen files.

I open the hard drive using Windows Explorer and sort them by Name.

I need to know if we can create a text file of the contents of the hard
drive, so that I can import it in Excel 2007 and place checkmarks on an
adjacent column to keep track of what files I'm testing. How do I do that? It
is possible? How?

Trying to concert wb3 files from quattro pro into excel 2007. is there any
way to do this?

First of all I want to thank all of those responding to these questions.
Thank you so much for your time! You guys are livesavers!

Here's the issue that I'm having. A group of my users is opening an Excel
2007 file that has external links to other Excel files. In addition, the
linked Excel spreadsheets contain secondary or tertiary links to other Excel
spreadsheets. Not all of the spreadsheets are in Excel 2007 format. Some of
them are Excel 2003. At some point in the process, one of the users began
updating the Excel 2003 files to Excel 2007 format. But, not all of them were
migrated. This caused the calculations in the main (for our example I am
calling it "Master") spreadsheet to fail. All Cells had the following error:

We initially thought it may be the links, so we cleaned up the Master
Spreadsheet's Links and verified them all. But, when trying to update the
links (either automatically by changing the setting to "enable automatic
updating of links") or manually (by clicking Links on the data ribbon and
manually updating them) the links showed an OK status, but the values in the
Master Spreadsheet's cells still contained the error "#VALUE!".

I ran across a suggestion in another thread to open all the linked
spreadsheets and the master spreadsheet and then click CTRL+ALT+F9 to force a
recalculation of all cells in all spreadsheets. This did update the Master
Spreadsheet with the correct values and the error went away. However, when
you attempt to make any changes to the Master Spreadsheet and save the
changes, the error reappears and the calculations do not work.

I have not tried to open every file past the first level of links at the
same time as of yet. I may do that next. I know for a fact that there is the
Master Spreadsheet with at least three levels of linked spreadsheets or more.
And I know that the linked spreadsheets contain both versions of Excel.

My question is, how do I fix this so that it quits breaking every time
changes are made to the master spreadsheet?

Thank you again to anyone that has any suggestions!


I have a pdf file that I want to convert a table in the middle of the the document to excel 2007. The pdf table that I want to work with is in a users manual from my company's main customer. This user manual has page headers and page footers in it. The customer said the only way I will get the data for the work I need to complete is to convert the 297 pages from their pdf document to excel for me to work with. .
I work some a small company that does not have a budget for buying adobe acrobat. It looks like adobe acrobat would pull out all the tables I want to access easily. I have tried a few free online websites that convert a pdf file to excel, but they do not work. Do you know of any free website where I it would complete my goal of converting a pdf file to excel?
The only solution that I found that works but is a bad option is the following:
I select the part of the table I want for each page. (I need to repeat this process 297 times). I hit control c, I then have a word 2007 document and do a control v into the word document. From the word document I select what I just pasted into it, I then do a contol c from the word document into the excel 2007 excel sheet. I then do a control v into the excel document and that page of the data is formatted correctly.

Opening an .xls file with Excel 2007 from Internet Explorer does not open it maximized

Hi there,

We are upgrading from Office 2003 to Office 2007. We have noticed that opening .xls documents with Excel 2007 from Internet Explorer does not open the document maximized within the Excel application window.

This is causing problems for our users because depending on how the window is displayed, the horizontal and vertical scrollbars are not shown against the edges of the Excel application window. As such, the only way to maximize the window is by double clicking on the document's title bar -- but most users would not be aware of this feature. Here is a screenshot of what I mean:

This problem does not occur with local files. In fact, while it may occur once with a local file, after maximizing the document window inside Excel and closing Excel, reopening the document causes Excel to remember that it was last maximized somehow and therefore displays it maximized.

We can reproduce the problem by opening any .xls from Internet Explorer 8 with Excel 2007 on Windows XP, Windows 7, Windows Server 2008 x64 (running Terminal Services/Remote Desktop Services). E.g., here are a bunch of .xls files you can test with from Bing:

Has anyone got a solution to this?


Hello all,

My company is thinking of upgrading our computers and I wanted to get advice on some things excel related. We work with large excel files (10 MB – 60 MB), usually with three or more open at the same time; at least one of these will have many small charts in it. Occasionally, things will run very slow or we’ll get “Excel cannot complete this task with available resources.”

1. Do you think upgrading to Excel 2007 will noticeably improve performance?

2. Should we upgrade from XP to Vista when it comes out?

3. Which is most important when dealing with large excel files: processor speed, memory, or graphics? I currently use a Pentium 4, 2.8 GHz, with 1 GB of memory.

Thanks for your help.

I saw some posts on here about similarites and one even linked to a converter
that works but adds garbage to the file unles you buy the full version.

Basicaly I just migrated a NT 4 workstation with Works 4.0 on it to a new XP
machine with Office 2003. We found a converter for the Works documents but
have had no real luck with the wks or Works spreadsheet files.

The rlsoftware converter adds advertising of sorts inside of cells and can't
be easily replaced.

Is there anything out there that works?

I have an expense report form that contains formulas. When I open the Excel file in Excel 2007, one formula no longer works.

Here is the formula: =CountOfCF(G9:G40,1)

This formula counts the number of red-colored numbers in a block of cells (G9 to G40). The numbers are red because their value happens to be greater than $76.99.

I receive a NAME? error when I open the report in the 2007 version of Excel.

Can anyone help?


How do I convert Quattro Pro 9.0 files to Excel?

Is there a way to open a Lotus 123 file in Excel 2007?

We began using Lotus 123 many years ago and still use quite a few of these files everyday. A large number of the files have been converted to Excel but we still have a lot in the Lotus format. We recently began using Excel 2007 and have found that we can't even open the files in order to convert them.

Thanks for the advice,