Free Microsoft Excel 2013 Quick Reference

Excel not able to open file Results

Dear all,

I have some VBA code (shown below) which allows the import of a large text file into Excel 2007, so that when it reaches the bottom of the sheet, it resumes importing on a new sheet. However, I also want to import data side by side (i.e. starting at a different column), but the code opens up a new worksheet, meaning that the data are imported onto separate sheets and not next to each other.

I've tried removing the 'Workbooks.Add template:=xlWorksheet' row in the code - this solves the problem for the first sheet, however, when the data to be imported runs onto the next sheet, the code opens up a n new worksheet (as before).

So my question is, how can the code be tweaked so that it doesn't start a new worksheet upon reaching the end of the previous sheet during a large import - I want it to overspill onto a different worksheet as before, but into the existing 'Sheet 2' as opposed to a newly created sheet.

Hope you are able to help - the existing code is shown below.

Many thanks,
smurray444

Code:
'"Text Files (*.txt),*.txt
Option Explicit
Sub LargeFileImport()
     Const MaxRows As Long = 1048576
     'Dimension Variables
     Dim ResultStr As String
     Dim FileName As String
     Dim FileNum As Integer
     Dim Counter As Double
     Dim num() As Variant
     Dim v As Variant, i As Long, j As Long
     Dim s As String, sChr As String
     Dim rw As Long
     'Ask User for File's Name
     FileName = Application.GetOpenFilename( _
       FileFilter:="Text Files (*.txt),*.txt")
     'Check for no entry
     If FileName = "" Then End
     'Get Next Available File Handle Number
     FileNum = FreeFile()
     'Open Text File For Input
     Open FileName For Input As #FileNum
     'Turn Screen Updating Off
     'Application.ScreenUpdating = False
     'Create A New WorkBook With One Worksheet In It
     Workbooks.Add template:=xlWorksheet
     'Set The Counter to 1
     Counter = 1
     'Loop Until the End Of File Is Reached
     s = ""
     rw = 1
     Do While Seek(FileNum)  0 Then
               v = Split(Application.Trim(s), " ")
               ReDim num(LBound(v) To UBound(v))
               For j = LBound(v) To UBound(v)
                 num(j) = v(j)
               Next
               ' Change 'rw' no. to alter import starting column
               Cells(rw, 1).Resize(1, _
                 UBound(v) - LBound(v) + 1) = num
               rw = rw + 1
               s = ""
               Erase v
               If rw > MaxRows Then
                 ActiveWorkbook.Sheets.Add
                 rw = 1
               End If
             End If
           Else
              s = s & sChr
           End If
         Next
         'Increment the Counter By 1
         Counter = Counter + 1
       '  If Counter > 1E+307 Then
       '    Exit Do
       '  End If
     'Start Again At Top Of 'Do While' Statement
     Loop
     'Close The Open Text File
     Close
     If Len(Trim(s)) > 0 Then
       v = Split(Application.Trim(s), " ")
       ReDim num(LBound(v) To UBound(v))
       For j = LBound(v) To UBound(v)
         num(j) = CSng(v(j))
       Next
       ' Change 'rw' no. to alter import starting column
       Cells(rw, 1).Resize(1, _
         UBound(v) - LBound(v) + 1) = num
       rw = rw + 1
       s = ""
       Erase v
       If rw > 1048576 Then
         ActiveWorkbook.Sheets.Add
         rw = 1
       End If
     End If
     
     'Remove Message From Status Bar
     Application.StatusBar = False
  End Sub


Hi all

I am just wondering if anyone has any tips an tricks concerning making excel not look excel.

Ive created a worksheet which will be used a lot i want it to look as little like excel as possible.

In the workbook open event i have a few line to make it a full screen and remove gridlines, any other suggestions greatly appreciated.

In particular is there code so i can remove every menu from excel but....I only need the user to be able to do a file save??....

Thanks for your input, i appreciate it (As always)

Curse you excel!! I save a page as a webpage, choose save graph only (above the box that has a checkbox to add interactivity) and excel autmatically names the .gif that is produced AND controls where it goes in my files (creating a file in the process).

Here is how to create my problem: Make a new workbook, and delete sheet 1 and 2. Make a graph (the data can be on the same page but mine is in a separate master workbook.) Save as Webpage. Choose to publish/save graph only. Give it a name (say JacksStats)

Excel creates a file called "JacksStats_Files" and and html doc saved as "JacksStats" Inside the the file "JacksStats_Files" are 2 items: (1) "filelist" and (2) "Book1_1884_image001" (or whatever your excel names it)

This image (naming) I need more control of. Basically I want a macro that saves 100 different graphs, on 100 different worksheets, and names each of these by name (MaryStats; FredStats, JoeStats, etc.) So when I ftp the file of (TeamStats, which has all individual 100 charts inside it) to my website, the updated graphs with each day's new info is always in the right place. But if I have to hand rename all these each day that would be impossoble. So, either:

(1) I need a way to control what excel names these image files AND to be able to control where it saves them (although I could work around that if I could just control the name) OR
(2) I need a whole new workaround-- I am having a hard time imagining what this could be but I am open to all suggestions! (I don't want the entire websheet "Jack's stats, because excel saves with lots of other gunk and I want control over how the and where the gif is placed on my webpage. so in theory it is best if I get control over the gif and not use the filelist, the html page, the extra file folder and the image...)

Thanks!!!

Dear all,

I have some VBA code which reads in a long text file into Excel 2007, and when it reaches the bottom of the worksheet creates a new one and carries on importing until reaching the end of the file.

However, it only reads in a single file at a time. I was wondering if it would be possible to automate the reading in of all of my 29 files in one go (where the code increments the file name by one each time from 1961 up to 1990): the file name format is out_lpj_year1961.txt, out_lpj_year1962.txt, out_lpj_1963.txt up to out_lpj_1990.txt.

Each text file is composed of 3 columns; for the first file to be imported (out_lpj_year1961.txt) I need all 3 columns going into Excel. Yet for the rest, I need only the third column being inserted in next to the existing column (i.e. the row count shouldn't increase, only the number of columns). The total column count should equal 31 (29 files of which the 3rd column from each one is imported, plus the extra two from the 1st file).

The code as it stands is:

Attribute VB_Name = "Module1"
'"Text Files (*.txt),*.txt
Option Explicit
Sub LargeFileImport()
Const MaxRows As Long = 1048576
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim num() As Single
Dim v As Variant, i As Long, j As Long
Dim s As String, sChr As String
Dim rw As Long
'Ask User for File's Name
FileName = Application.GetOpenFilename( _
FileFilter:="Text Files (*.txt),*.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
'Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
s = ""
rw = 1
Do While Seek(FileNum) 0 Then
v = Split(Application.Trim(s), " ")
ReDim num(LBound(v) To UBound(v))
For j = LBound(v) To UBound(v)
num(j) = CSng(v(j))
Next
Cells(rw, 1).Resize(1, _
UBound(v) - LBound(v) + 1) = num
rw = rw + 1
s = ""
Erase v
If rw > MaxRows Then
ActiveWorkbook.Sheets.Add
rw = 1
End If
End If
Else
s = s & sChr
End If
Next
'Increment the Counter By 1
Counter = Counter + 1
' If Counter > 1E+307 Then
' Exit Do
' End If
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
If Len(Trim(s)) > 0 Then
v = Split(Application.Trim(s), " ")
ReDim num(LBound(v) To UBound(v))
For j = LBound(v) To UBound(v)
num(j) = CSng(v(j))
Next
Cells(rw, 1).Resize(1, _
UBound(v) - LBound(v) + 1) = num
rw = rw + 1
s = ""
Erase v
If rw > 1048576 Then
ActiveWorkbook.Sheets.Add
rw = 1
End If
End If
'Remove Message From Status Bar
Application.StatusBar = False
End Sub

Just to add that I didn't write this code myself! I'm useless at VBA; this was taken from the MS website and adapted slightly by someone who knows far more about VBA than me!

If you would be able to make the amendments I've suggested, then I'd be very grateful. Also if you need any further clarification then please do not hesitate to ask me.

Many thanks for your help and time,

Steve

I have made an excel file the use of which I want to restrict to a few people. So I want that whenever the user will open the file it will prompt for user id and password. If these matches then he will be allowed to access the file. Thre types of user id and password is required:

User! : Can modify, edit, delete, etc ie full access (Adminstrator type)
User2: Only access to print (no entry,edit,modifiaction,etc)
User3: Only be able to see the file and no print,entry,etc access.

If all are not possible, then provide code for part.

Macro will always be enabled as I found code on the net to force user to enable macro. ie if macros are not enabled, it shows another rough page. So enabling macro is not a problem.

I want to be able to prevent Excel from recalculating formulas when opening a workbook with a newer version. This is not a one time need or on a case-by-case basis. The following KB article comes close but is for a single document - I need it blindly for all documents that are opened:

http://support.microsoft.com/kb/289111

Here is the reason I need this. I have a process that opens and auto prints many Excel files (from email attachments). A lot of them are from newer versions, but some are from older versions. When older versions open and then print, it leaves the Save prompt because formulas were 'recalculated'. This kills the process because it never closes to older version file. Does anyone have any ideas that could help? Thanks for looking.

Hello Guys,

I have an Excel with multiple worksheet. Some of the worksheets are populated using External Data thru MS query. and Other worksheets are attached and summarized with these external data worksheet.
=========
I populate a worksheet(COL A thru COL O) and then creates 4 additional columns (say COL P, COL Q, COL R AND COL T. COL P is the concatenation of COLC+ YEAR/MONTH FROM COLB, COL Q concatenation of (COL A,COL C), COL R is the SUM of Col N+ COL O and Col T IS THE sum of COL D.

When I entered concat formula for COL Q in cell Q2 - no problem.
When I entered concat formula for COL R in cell R2 - no problem.
and now I clicked REFRESH ALL BUTTON, everything works fine. Point to be noted here is Still I didn't entered formula for COL P. I save the file and gets out. No issues

I open the file again no problem, everything looks ok and in place. No I entered concat formula for COL P in cell P2 , and save, No problem

My problem at this stage of game
=======================
When I click the REFRESH ALL button again, all the values gets replaced in the COL P2, Q2 and R2 with LABEL FOR COL P, COL Q AND COL R.

There is no macro or VBA code running behind the scene. I am not able to figure out how the P2, Q2, R2 cell gets populated with the col LABELs. I have trying to figure out this but so far no success..

Please let me know if everything is clear here and any help will be greatly appreciated!

sba

Hi everyone,

I've got a bit of an issue with Excel 2003 updating cells even when I choose not to do so.

Here's the situation:

We have an excel file that's updated frequently on a Bloomberg terminal. Excel on the Bloomberg terminal (which is a system running Windows XP, Office 2003) contains the Bloomberg dde addin that allows the file to update with information pulled directly from Bloomberg.

Our users are still running Windows 2000, Office 2000. They are able to open the Bloomberg-created file on their W2K systems - they get a prompt indicating that there are links to other files and asking if they want to update, YES or NO. Choosing NO leaves the data in the cell intact, and no updates take place.

We are in the process of migrating to WinXP and Office 2003, and have run into some issues. Basically the process is the same - however in Excel 2003, when users choose DON'T UPDATE at the "do you want to update links" prompt, some kind of updating takes places anyway and the cells are left displaying #NAME?.

Is there any way to stop this and have Office 2003 behave like Office 2000 did? Our current workaround to this situation is to open a blank excel worksheet, go to Tools/Options/Calculation and set the update to "manual". Then go to File/Open and open the Bloomberg spreadsheet. However, our users want the "automatic" option to still be enabled for their work!

Basically, we want to have the "automatic" option enabled by default in Excel 2003 for all our spreadsheets, but we also want our spreadsheets to not update when we choose not to. Is this possible?

Thank you for any advice in advance,
Jeff

hey,

can anybody remember seeing some useful info in here on taking data from an excel sheet and exporting it into several excel sheets in other workbooks ?

i have some reports in excel, and need to get range A1:J60, A61:J120 and so on to do the following somehow in code :

'report workbook is called report on sheet report in C:report

Sheets("report").Select
Sheets("report").Range("A1:J60").Select
Selection.copy

' theoretical code
if dir("c:town report") does not exist, create dir ("C:town report")
ELSE if workbook("C:town report") & Range("a3").value does not exit
' range a3 equals the year of the report
--- then create workbook("C:town report") & range("a3").value
' next step
then open workbook("C:townreport") & Range("a3").value
addsheet
sheetname = C:report.xls[Report]$A$2
' A2 in the reports sheet is the month of the report
selection paste

so theoretically, it selects a range from a report, creates a workbook titled by the year the report was in, makes a sheet in that report based on the month the report was in, and pastes the report into the new sheet

sounds like a big job, but it's just getting your head around the code that takes the time

so if you've seen anything seriously helpful on exporting sheets and creating new ones, then plz let me know, as it's gonna be a pretty cool piece of code.

some ppl here may even be able to work it out in their head. ( i dont even know where to begin )

scary...

I'm going crazy!!!

I need to do some v-lookups on some files and then update some Access databases.

I have SKU#. However, some SKU's may have letters as well, therefore I defined it in access as a Text. I set the format to Text in Excel. I copied the data and repasted it. I even went as far as opening a new worksheet, making the whole sheet text and then copying and pasting the values of the SKU.

most SKU's are numbers, but some are alpha numeric. I did a v-lookup and still no luck, I did a text to columns, no luck. When I right click on the cell in access and lookup the format, it says 'Text', but when I load into access it won't load anything with a letter in it.

My vlookups won't work either. Can I turn off the feature of Excel treating a text as a number? It usually gives me a message and shows the little green error message, but it is not doing this.

I tried adding SKU 1 + SKU 2 and it added them for me. If they were really text fields it wouldn't be able to do this.

HELP!!!

Greetings all, I'm looking into creating a file for work, and I'm not sure if I should be looking at using excel, access, or perhaps something else entirely. In a nutshell I want to have a database with rows for
part number
description
fixture location
program number
notes

I would also like to be able to do searches limited to one column or another, sort the entire database based on one column or another(with the row data staying matched up), and most importantly be able to click on the program number or notes or part number and open the matching file in a given application.

Any suggestions on what program to look into using? I'm thinking access, but I haven't used it yet, and I'm thinking it might be overkill from the little I browsed around the help file.

Thanks in advance.

Von Pookie's HTML Maker FAQ
Last updated: 07 July 2009

07 July 2009: Downloads are currently unavailable for Colo's site. If you would be interested in testing the new HTML Maker designed specifically for the MrExcel forum, please see this thread:
http://www.mrexcel.com/forum/showthread.php?t=399380

PLEASE NOTE: This FAQ was written while we were using phpbb forum software. I have not checked these instructions for accuracy with the new vBulletin software we are currently using. It is possible that some items listed here may no longer apply.

Please note the following:
This is not the FAQ for the VBHTML Maker nor the YaBB Table Maker.These directions were written using Windows 2000 and XP, and Excel 2002 and 2003. I do not (currently) know the information for Macs or any other versions of Windows or Excel.

How can I show a small image of my worksheet in my post?
You can simply download the handy HTML Maker add-in! The HTML Maker is available for download at Colo's site. Currently unavailable

I have downloaded the .zip file. What is the next step?
You may need to download an extraction program such as WinZip to open the .zip file and extract the add-in file. For now, just extract the HtmlMaker.xla file to your desktop. Once you have this, you no longer need the .zip file.

Next, you need to locate the Addins folder on your computer. There are several places you may see a folder with this name. From the Excel help file: "Add-ins are stored by default in one of the following places:
The Library folder or one of its subfolders in the Microsoft OfficeOffice folder.The Documents and Settingsuser nameApplication DataMicrosoftAddIns folder."If you're uncertain which folder to use, open Excel, go to the Tools menu and select Add-ins. In the dialog that will display, click the Browse button to see the path for the AddIns folder it is using. Once you've located this, you can simply drag and drop the HtmlMaker.xla file into it.

Note: If you cannot find the Application Data folder, you probably just need to change a setting on your computer.Open Windows Explorer (or any folder) and select Folder Options from the Tools menu.On the View tab, look for "Hidden files and folders" under the Advanced SettingsMake sure the option "Show hidden files and folders" is selected, click OK, and look for that pesky Application Data folder again.

I have added the file to the AddIns folder, but I am unable to activate it in Excel!
Start Excel, go to the Tools menu and select Add-Ins. You should see a dialog box with a list of available add-ins, with a checkbox next to each item. Simply find the one labeled Htmlmaker, check the box next to it and click OK. You should now have a new menu between the Window and Help menus called HTML. You have now installed and activated the HTML Maker, and should be ready to go!

How do I use this add-in?
Using the HTML Maker to post a "snapshot" of your worksheet on the message board is relatively simple.
[list=1][*]Make sure you have enabled HTML in your board profile.Click the "profile" link--which is in the group of text links at the top-right of every page of the board.In the list of preferences, make sure the option for "Always allow HTML" is set to Yes.Click the "Submit" button to save any changes to your profile[*]Make sure the HTML Maker add-in is active (see "I've added the file to the AddIns folder, but I can't figure out how to activate it in Excel!").[*]Go to the HTML menu and select "Convert sheet to HTML"[*]Select the range you want to copy
Please select only the range (preferably with little to no formatting) that is crucial to the question you are asking.[*]Click OK[*]On the message box that pops up, click "yes" (I honestly have no clue what this does, but I always click yes and it works just fine)[*]Another message box will appear, letting you know how many characters the HTML Maker code will use, and how many characters that are remaining for you to use in typing the rest of your message. Click OK.[*]A new browser window will open, displaying what your copied cells will look like when posted to the board.[*]In this new window, click the "Copy code to clipboard" button.
If this does not work...[list=a][*]If you are running Windows XP with Service Pack 2 installed, make sure you allow popups on the page (they will be blocked by default) and try clicking the button again[*]As an alternative:In this same window click the button that is on the page that is labeled "View Source." This is located right next to the "Copy to Clipboard" button.Copy the entire code that is displayed there.[/list:o:4e41f3da2f][*]Come back to the board where you are entering your message, and put your cursor in the area where you type the body of your message.[*]Press Ctrl+V in the area where you type. If you don't know HTML, it will look like a lot of gibberish. That's normal, don't worry about it. You can still edit and add to your message if needed at this point. Do not edit/add to the code from the HTML Maker.[*]When you are finished, click the Submit button to post your message. The table should appear in your post just as it did on the browser page you copied the code from.
Do not preview a message containing HTML Maker code. You will not be able to use the buttons on the preview page to submit your message.Do not quote a post using HTML Maker code. You will receive errors (when quoting a message, simply edit out any extra HTML Maker code).[/list:o:4e41f3da2f]Back to top

Known Issues & Errors

I installed the HTML Maker add-in, but the "HTML" menu you say should appear on the menu bar is not there.
It is possible that you have installed the wrong add-in. This FAQ is for Colo's HTML Maker, NOT the VB HTML Maker or YaBB Table Maker. Making sure you have installed the correct add-in is the first step.

If you have confirmed that you installed Colo's HTML Maker correctly, make sure you have enabled the add-in. Go to ToolsAdd-Ins, click the checkbox for the HTML Maker and click OK. The menu should then appear.

If you have performed both of the previous steps and the menu is still not appearing, post your questions in the Questions About This Board forum.

I followed the directions, but after posting the page is not displaying correctly.
You can either edit the post yourself and remove ALL of the HTML code, or report the post by clicking the "Report" button () on the post to notify the moderators of the problem. A moderator will edit the post to remove any/all HTML Maker code so the thread will be able to display correctly.

As to why the page displayed strangely in the first place, it's usually one of two things: either you didn't follow the directions and copied the source of the page the HTML Maker generated instead of using the "view source" button on the page (yes, there is a difference and we can tell from the code if this is the case), or you have inadvertently posted incomplete HTML Maker code--which is much more common.

HTML requires both opening and closing tags; if you do not post the entire code, the closing tags are not included and the board cannot interpret the code correctly.

When using an HTML Maker example in your post make sure that you have copied all of the generated code and that it is pasted into the message in its entirety. There is also a character limit for each message (but to be completely honest, I have no idea what that limit actually *is*). If you try to use multiple examples within a single post, you can hit the limit quickly and the end of the code could be cutoff.

Error: "Could not load an object because it is not available on this machine"
You may be missing a file called ietimer.ocx, which is required. Click here to go to Colo's site and check if the file is already installed on your computer. This link will also automatically install the file for you if it is needed.
The file is also available for download from Microsoft: http://activex.microsoft.com/control...86/ietimer.cab After downloading the file, it must be registered with regsvr32. The easiest way to do this is to right-click the file and select Install from the context menu.

Error: "Compile error." or "Can't find project or library..."
Open the Visual Basic Editor by pressing Alt+F11. Once in the editor, select the HTMLMaker in the Project window then go to the Tools menu and select References.
If there are any references that start with "MISSING," uncheck them and don't forget to save.
More information on this error can be found in this thread: http://www.mrexcel.com/board2/viewtopic.php?t=107231

Error: "Error Number: 91, Object Variable or with Block variable not set..."
Click OK to close the dialog box with the error and go to Tools->Macros->Security. Click the Trusted Sources tab and check the box next to "trust access to Visual Basic Project," and click OK.

In Excel 2007, the directions are slightly different:
Click the Office Button and go to Excel Options->Trust Center->Trust Center Settings->Macro Settings->Developer Macro Settings. Then you can Check the "Trust access to the VBA project object model" box and click OK.
(Thanks to Bradlee_S for posting this, because I would have never thought to add it otherwise)

Error: "Runtime error: 5 Invalid procedure call or argument."
When this error occurs, the following line will be highlighted in the debugger:
Code:
Open the Visual Basic Editor (Alt+F11) and go to Tools->Options. Select the General tab and make sure "Break on unhandled
errors" is selected for "Error Trapping."

First mention of this error (and solution) can be found in this thread:
http://www.mrexcel.com/board2/viewtopic.php?t=242967

I am unable to view formulas after clicking the formula link in the posted example
There are two or more posts in the thread with HTML Maker examples, which could cause a conflict in the Javascript code being used. There is no real "fix" for this issue that I know of. The only real workarounds are to not post a message using the HTML Maker code in a thread already using an example elsewhere, or if you absolutely need to show how the data is set up, use the HTML Maker to show your sheet and indicate what formulas are in what cells within your message. Users viewing the examples in Firefox may notice this problem as well. (I have this issue with Firefox no matter what, personally).

I am unable to copy the generated code to the clipboard.
As was discovered by board member Parry: "If you have installed Windows XP Service Pack 2, a Pop-Up blocker is installed [in Internet Explorer] as part of the pack and by default this will block active content.

When you select your cells and run HTML Maker it will create an HTML page of your image as expected, but the pop-up blocker will prevent you copying the code when you click the button to send Source to the clipboard. Simply click on the pop-up security information bar, then select Allow Blocked Content and you can now copy the HTML to the clipboard."

Back to top

Miscellaneous
[list][*]This add-in is simply to show a small "snapshot" of the selected area of your spreadsheet. However, some things will not display when posting an HTML Maker example, such as:Drawings and objects (i.e., items created from the Drawing toolbar)Clip art and other imagesCharts and graphsConditional formatting[/list:o:4e41f3da2f]Questions concerning the HTML Maker should be posted in the Questions About This Board forum.
If you have not used the HTML Maker add-in before, please post in the Test forum before making your actual post to make sure the example will display correctly. If any posts with HTML maker code end up "breaking" the page (including posts in the Testing forum), they will be edited by a moderator or administrator to remove the problem-causing HTML code from your post or the post itself will be deleted.

Find an error or something I missed?
Click to send me a private message

When I open 2 workbooks in 2 instances of Excel on my computer, I can not
copy a sheet from one book to the other. It is also not possible to tile the
2 files.
When I open both files in the same instance, it it possible to select the
other file in the dropdown list.
Since I usually open the files from Explorer, I would like to be able to
tile & copy when multiple files are opened.
What setting should I change?

I have been using my PC with excel program for 3 years and when I tried to
open an excel spreadsheat today and it would not open. "Installation error:
file not found" appeared. File not found was SKU011.CAB and I have not been
able to reinstall as I don't have the installation CD as I bought the PC
second hand with programs on it. Can anyone help?

Just a suggestion.

When working with large multi-tab excel files, it would be nice to be able
to arrange tabs in a meaningful way - grouping or stacking or inserting
spacers. Also, tab shapes and fonts should be flexible.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...ic.excel.setup

Hi Everyone,

I am having trouble constructing a block of code that is intended to
open a workbook and read some sheet data the worksheets in it. The
code works fine when it is early bound, but when shifting to late
binding there is a problem with the .EnableEvents changing state from
False to True when I open the first workbook. This is a problem
because the workbooks are designed to start-up with a form, and when
the code opens the workbook the form is opened with it but behind all
other forms - it should not be there at all as I understand it.

My code is as follows (Standard Module):

Option Explicit

Const xlWorksheet As Long = -4167
Const xlChartSheet As Long = 3

Sub test()

Dim XL As Object 'Excel itself
Dim WB As Object 'Workbook
Dim ws As Object 'Worksheets Collection
Dim ss As Object 'Spreadsheet (not set, just stays as object)

Dim folder As String
Dim Files As Variant
Dim Filename As String

Dim i As Long

Dim Proceed As Boolean

On Error GoTo ErrorHandler

folder = BrowseFolder
Files = GetAllFilesInDir(folder)

Set XL = CreateObject("Excel.Application")

XL.DisplayAlerts = False
XL.Application.DisplayAlerts = False
XL.Application.EnableEvents = False
XL.EnableEvents = False

For i = 0 To UBound(Files)
If Right(Files(i), 3) = "xls" Then

Filename = folder & "" & Files(i)

Set WB = XL.Workbooks.Open(Filename)

Set ws = XL.ActiveWorkbook.Sheets

For Each ss In ws
Proceed = SheetTest(ss)
Debug.Print Filename & vbTab & vbTab & ss.Name & vbTab &
CStr(Proceed)
Next

Set ws = Nothing
WB.Close savechanges:=False
End If
Next

Set ws = Nothing
Set WB = Nothing
Set XL = Nothing

Exit Sub

ErrorHandler:
Debug.Print Error & vbTab & Err
Stop
Resume Next
End Sub

I will eventually shift this to a class module when I can get it to
work. Is there any reason why the .EnableEvents = False is not
remaining in the state it is set to for the Excel.Application object?
I need to stop these workbook events from being fired. Is there
another way? It would be great to be able to take this code and use it
in other MS Office applications and keep it version inpedant at the
same time (ie/ using late binding to avoid dependancies).

Using Excel 2000 9.0.8950 SP-3

Any help greatly appreciated. I am really stumped.

The Frog

I have an Excel document I just received. It opens fine, but I cannot click
on any field to edit it, though I should be able to. The person who created
the document also has the same problem.

It does say "Compatability Mode" at the top, but not sure why. I can save
the file as different versions, .xls, .xlsx. Both versions say
"Compatability Mode"

Using Excel 2007.

Appreciate the help!

Christian

Can not figure this out for the life of me.

At work here, there is a folder on the network with some Excel files. Three
people are trying to open up and edit these (One at a time, to my knowledge).

Person 1 and Person 2 can open up and edit all of the files in that network
folder, however, Person 3 can edit 3 out of the 5 files no problem. The other
2 files, if person 3 tries to edit them, gets a pop up box titled "Unlock
Range" "Enter the password to change this cell:" no matter what cell Person 3
tries to edit.

Doesn't matter if Person 3 tries to double click a cell, right click a cell
or try and edit the cell from the formula line, it always pops this box up. I
tried a few passwords and even tried pressing ok when it was blank, but to no
avail.

I even tried copying this file off of the network to my local computer, and
I get the same pop up box.

The file properties outside of Excel are not marked "Read only" or any other
attributes that would limit it.

I have checked everything possible that has to do with this problem
according to Microsoft's help, so I now turn to you guys.

Something interesting, under the Tools menu, then Protection, the option
titled "Allow Users to Edit Ranges..." is grayed out so I am not able to
select it, though this sounds like the exact option that I need. One of the
other options called "Unprotect worksheet" asks me for a password also, which
I have tried a few and leaving it blank too.

I can not get to edit these files except for on a select few employees
computers where I do not get any of these prompts at all!

This is so bizarre! Has anyone else had this problem? And found out a way to
fix it? I have uploaded a picture of some of the options I have already
checked to give you guys a better idea of what I have tried to do already.

The image is located at:
http://www.we-todd-did-racing.com/vi.../1005596_1sevm

Please help! If you guys need any more details, feel free to ask away. I
will try and check in every now and then to see the replies!

Thanks so much (In advance)! I appreciate it!

Excel 2003 SP2

The problem relates to a workbook not being able to access data in a linked
workbook that resides in a different sub-directory.

I have five workbooks each created and residing in the same sub-directory.

Workbook 1 is a 'Menu'.
Workbooks 2, 3 & 4 are a 'Form'
Workbook 5 contains 'Data'

When the 'Menu' workbook is opened, a macro automatically opens the password
to open 'Data' workbook with property Visible = False.

Buttons on the 'Menu' open the 'Form' workbooks as Read Only files. The
'Form' workbooks are linked to the 'Data' File.

When the 'Form' is complete it is saved to another sub-directory. The
'Form' is not saved as a read only file. The 'Form' is then closed; then
the 'Menu' closed which automatically shuts down the data file.

Access to the saved 'Form' is independent of the 'Menu'. When the saved
'Form' is opened it does not read the 'Data' file. The formulae in the
cells in the 'Form' workbook are looking for the 'Data' file in the same
sub-directory. Not the sub-directory containing the 'Menu', 'Forms' and
'Data' files.

Normally when an Excel workbook is saved into a different sub-directory to
the linked workbook the link is maintained.

Inspection of the formulae in the 'Form' workbook once saved, but before it
is closed, shows the formulae correctly pointing to the cells in the linked
'Data' file.

Can anyone please explain why and advise a solution to the fact that the
formulae in the 'Form' workbooks are no long looking in the correct
sub-directory.

Many thanks
--
Derek Dowle

Hi, I've currently got this problem at work - any help would be
appreciated.

I've exported a CSV file of payer information from one of our systems
with the plan of being able to manipulate this data and import back. It
only allows the choice of saving it as a txt file which i do. When I
open this in notepad the charactors appear somewhat like this (I'm
going from memory here):

, "0027892", "Mr & Mrs Bloggs", 0, 1, 1

I then started excel and opened the txt file with the use of import
wizard making sure I converted some of the columns to text so I didn't
remove the leading zeros.

I then tried to save this in excel as a csv file. However, a message
came up saying that part of the format was uncompatable but did I want
to save anyway,this I did.

Problem is whenever I save it as a txt file again with this aim of
importing the amended data, the leading comma and all the quotation
marks are gone. It appears something like this in notepad:

0027892, Mr & Mrs Bloggs, 0, 1, 1

Any ideas as to what I'm not doing correctly?

Sorry about the longwinded post.

Matt

--
rammstein
------------------------------------------------------------------------
rammstein's Profile: http://www.excelforum.com/member.php...o&userid=31869
View this thread: http://www.excelforum.com/showthread...hreadid=516001