I'm running a vba macro that has worked on previous computers (windows xp and office 2000), I'm now using windows 7 and office 2007.
The macro is designed to cycle through a number of windows, the amount defined using an inputbox function, taking data from each file and putting it into a summary file. Currently, the macro is only working for the first window and wont cycle through. It stops after the first sheet without registering an error. The program reads as follows:
Can anyone please help??Sheetall = InputBox("Number of worksheets to be analysed?") Sheettot= Val(Sheetall) For sheetnow= 1 To Sheettot Windows(sheetnow+ 1).Activate datanum = Cells(5, 2) '(here is the main body of the macro, which is working fine) 'The following section copies data from the main sheet into the summary sheet Windows("PavCondSummary").Activate x = 2 Do Until Cells(x, 1) = datanum x = x + 1 Loop Cells(x, 2) = CSptot Cells(x, 3) = CSmtot Cells(x, 4) = RItot Cells(x, 6) = CSplat Cells(x, 7) = CSpmiss Cells(x, 8) = CSmlat Cells(x, 9) = CSmmiss Next sheetnow
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.
You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file.
To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Hi, there!
Can not 100% understand idea behind this, but there is a code that will scroll through pre-set number of sheets. Hope it helps!
Private Sub CommandButton3_Click() Dim sh As Worksheet Counter = 0 Sheetall = InputBox("Number of worksheets to be analysed?") Sheettot = Val(Sheetall) For Each sh In Worksheets If Counter = Sheettot Then Exit Sub End If MsgBox ("fff " & sh.Name) sh.Activate Counter = Counter + 1 'datanum = Cells(5, 2) '(here is the main body of the macro, which is working fine) 'The following section copies data from the main sheet into the summary sheet 'Windows("PavCondSummary").Activate 'X = 2 'Do Until Cells(X, 1) = datanum ' X = X + 1 'Loop 'Cells(X, 2) = CSptot 'Cells(X, 3) = CSmtot 'Cells(X, 4) = RItot 'Cells(X, 6) = CSplat 'Cells(X, 7) = CSpmiss 'Cells(X, 8) = CSmlat 'Cells(X, 9) = CSmmiss Next sh End Sub
Hi,
Thanks for the advice, but it still doesn't seem to be working. Maybe a bit more context would be useful...
I'm a scientist and my computer generates data in individual files that can be imported into excel as a spreadsheet. On any given day, I can generate 40 files with hundreds of data variables to be analysed. My macro is designed to go though each data file, select/calculate the required data and export into a summary file.
Usually, and when I run the macro in Excel 2003 and Windows XP, I can open all 40 files and it will go through each of them. I assume this is a compatability issue more than a coding issue - but when running in Excel 2007 with Windows 7, it will only select one file and wont run through all 40. The macro itself runs successfully, and no errors messages are created.
Is this something to do with how the individual files are named in the new version of Excel?? I am totally lost at this point and data is piling up around my ears!!
Maybe context = sample workbooks would be helpful.
If a file has been saved in native format in 2007/2010, it will more than likely have a .xlsx file extension. If there is code in them, they will be .xlsm files.
Sheet naming has not changed as far as I am aware.
Regards, TMS
Hi,
The plot thickens...
I was trying to upload the files - but since they are in an unrecognised format your uploader couldn't find them - they are opened in Excel using the text import wizard. I converted a couple to .txt documents for this purpose - the macro now works!
However, converting hundreds of files into a different format doesn't really solve the problem so I've attached anyway so you can see what it's supposed to do!
Open PavCondSummary, then PavCondMacro (hidden workbook). The open the data files from within excel and use the import wizard. Boxes ticked should be 'Delimited' in the first window, and 'Tab' and 'Space' in the second. Then with the Summary file on the screen, run the macro. You can see that it will take data from all 3 files and copy into the Summary sheet.
Now... any ideas why this wouldn't work for an unspecified file format when they are imported into Excel in exactly the same way???
(P.S. I'm sorry if this is a step too far in terms of uploads - but I don't know how to explain it any clearer)!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks