Need Excel Macro to open files

Microsoft Excel 2003 (full product)
March 4, 2010 at 02:40:10
Specs: Windows XP
Hi everyone,

First I have search on mine own before asking this qeustion here :). I am using XP with Excel 2003.

My qeustion is next
I try to make a macro that works for multiple excels files called scans_0001.xls and it counts till the end.

I try to use ActieveSheet, or Myfile to get the file name of open excel file.

What I need is a macro that opens every scans_????.xls file and copy some single raws and paste in the total.xls.

Like:

TotaL:
raw 1: scans_0001.xls
raw 2:scans_0002.xls and
so on.

The scans has 7 sheets called, Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6 and overview.

I used =Sheet1!B1 in the macro the get info of active sheet and it works but not with macro for every single file.

So can some one help me please.
Thank you in advanced.

Greets,

Kutu


See More: Need Excel Macro to open files

Report •

#1
March 4, 2010 at 06:44:04
Hi,

I wonder if your problem is that you are using =Sheet1!B1, but you have not activated the appropriate workbook, so =Sheet1!B1 always looks at Sheet1 in the same workbook, also =Sheet1!B1 is the form used in worksheet cells not in VB code.

I assume that you have some form of For...Next loop to create the name of each workbook and open it
If it's like this:

For n = 1 to 10
	Workbooks.Open ("C:\MyPath\scans_" & Format(n, "0000") & ".xls")
Next n

Change it to create the workbook file name first and then use the filename to both open the workbook and reference it specifically:

For n = 1 to 10
	strWBname ="scans_" & Format(n, "0000") & ".xls"
	Workbooks.Open ("C:\MyPath\" & strWBname)
	x = Workbooks(strWBname).Worksheets("Sheet1").Range("B1").Value
Next n

By fully qualifying the cell with its worksheet and workbook names you will be sure of getting the data from the correct place.

If you still have problems, please post the code you are using.
Enter the code between <pre> and </pre> tags - you can insert them from the icon above the reply text box.

Regards


Report •

#2
March 4, 2010 at 07:30:26
First Thank you for the reaction!

I did not really understand what you mean but, what i tryed was, recording a macro that copy and past =Sheet1!B1 in to the new xls file.

all the files are already created so
"Change it to create the workbook file name first and then use the filename to both open the workbook and reference it specifically:"

what do you mean? I mean i have to sleep a night over because I cant thing clearly atm.

This was the test that failed

   Windows("Persnlk.xls").Activate
    Rows("1:2").Select
    Selection.Copy
    MyFileName = "file" & ".xls"
    Windows("ActiveSheet.Name").Activate
    Sheets("Sheet6").Select
    Sheets.Add
    Rows("1:2").Select
    ActiveSheet.Paste
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[-1]"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[-2]"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=Sheet3!R[-1]C[-4]"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=Sheet3!R[-1]C[-4]"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-1]C[-5]"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-1]C[-8]"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!RC[-6]"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!RC[-9]"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=Sheet5!R[-1]C[-11]"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!RC[-16]"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[-1]C[-13]"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[1]C[-19]"
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[2]C[-22]"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[3]C[-25]"
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[4]C[-28]"
    Range("AF2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[5]C[-31]"
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[6]C[-34]"
    Range("AJ2").Select
    ActiveCell.FormulaR1C1 = "=Sheet6!R[7]C[-35]"
    Range("N2").Select




Report •

#3
March 5, 2010 at 04:59:51
Hi,

I have had a look at the macro you recorded.

I assumed from your initial post that you had created a macro that opened each file (scan_0001.xls etc.) and obtained data from Sheet1, cell B1 in each workbook.

However your macro does not contain any references to the files scan_0001.xls etc. Your macro appears to do a number of things that you did not mention in your original post, including adding a new worksheet and adding formulas which link to cells such as AJ2. There is no reference to the workbook total.xls which you mentioned before and there are two files "Persnlk.xls" and "file.xls" which were not mentioned in your original post.

In your first post you say I try to use ActieveSheet, or Myfile to get the file name of open excel file.
Can you explain why you are trying to get the name of an open Excel file and what you are trying to do with the file name.

I think that it would be best if you stated exactly what you are trying to do again.
Try and be complete in your description, and use the real filenames and the actual cells to be copied.

You asked what I meant by "Change it to create the workbook file name first and then use the filename to both open the workbook and reference it specifically:"
and you said "all the files are already created"

I understand that all the files are already created, but to open them in a macro you must have all the names of the files to open. In my code snippet I had a loop that created a series of filenames and then opened each one.
What I was suggesting in this sentence was that you created the filenames such as "scans_0001.xls" and held the name in a variable (I called it strWBname as it is a text STRing for the WorkBook NAME) then you use strWBname as the name of the file to open, and then when copying from that file instead of trying to activate it and copy from it, you use the file's name in strWBname to reference the workbook specifically.

The reason for this was that copying cells or rows using the active worksheet is harder to control and harder to debug than referencing workbooks and worksheets by name.

Workbooks("scans_0001.xls").Worksheets("Sheet1").Range("B1") is longer than Activesheet.Range("B1") but it is much easier to be sure that the cell you are copying is the one you intended to copy.

If you have a scan file name in strWBname then you can use:
Workbooks(strWBname).Worksheets("Sheet1").Range("B1")
This can be used in a loop to get the contents of cell B1 from the full series of scans files.

Regards


Report •

Related Solutions

#4
March 8, 2010 at 02:43:41
I understand that i wasnt clear, its mine fault i was busy with more thing and not one thing.

OmniPAge(software) exports xls file with the name scans because there are more then one Onmipages makes it counter scans_0001.xls, scans_0002.xls.

The info in very xls :
Sheet 1 A1 B1
Sheet 2 B1 B2
Sheet 3 A1 B1
Sheet 4 B1 B2
Sheet 5 B1
Sheet 6 A1 A2 A3 A4 A5 A6 A7 A8 A9

I want to get these information one one general file. So if we take scans_0001.xls as example.

on row 1 i wanted every informatie of scans_0001.xls and on row 2 scans_0002.xls. etc.

I read that you give me another way of trying but i didnt get what you meant. Sorry for late reaction, i had holiday.

And Thank you for the reaction and tips you have been given to me


Report •

#5
March 8, 2010 at 13:39:47
Hi,

The following macro asks for the first and last numbers of the scan files e.g., 0001 and 0008
Then it opens each file.
The Path to the scan files is hardcoded in the following, just after the DIM statements

'setup Path to saved scan files - must end with \
strPath = "C:\Temp\"

Also near the start, the base name for the scan files is set:
'setup base name for scanned files (case sensitive)
strBase = "scans_"

The first row to use for the results is set here:
'set first row offset for saving data (Offset 0 is row 1)
intDestRowOffset = 1

Each scan file is opened, data is copied from the 6 worksheets in each one, as per your last response, with all data from one workbook going on one row of the destination workbook.

Each workbook is closed after copying and finally the destination workbook is saved.

Where you save this macro depends on how you intend to use it. If you are going to have several destination workbooks, then you may want to save the macro in Personal.xls (a hidden workbook which is usually available every time Excel is opened), so that the macro is always available. If you run this from Personal.xls, then this line will need to be changed to identify the name of the destination worksheet, otherwise it may default to "Personal.xls"

'setup name of destination file - use the name of this workbook
strDestFN = ActiveWorkbook.Name

If you intend to have only one destination workbook, then you could add a command button to it and attach the code to the button. If you use this approach it would be possible to change the code so that each time it is run, the data from the new scan files are added below existing data, rather than always starting at the same row every time.

Option Explicit
Sub GetScans()
Dim strFirstScan As String
Dim strSecondScan As String
Dim strLastScan As String
Dim blnRedo As Boolean
Dim strPath As String
Dim strBase As String
Dim strThisFilename As String
Dim strThisFile As String
Dim strDestFN As String
Dim intDestRowOffset As Integer
Dim intDestColOffset As Integer
Dim n As Integer

On Error GoTo ErrHnd

'stop screen flicker during copy and paste operations
Application.ScreenUpdating = False

'setup name of destination file - use the name of this workbook
strDestFN = ActiveWorkbook.Name

'setup Path to saved scan files - must end with \
strPath = "C:\Temp\"

'setup base name for scanned files (case sensitive)
strBase = "scans_"

'set first row offset for saving data (Offset 0 is row 1)
intDestRowOffset = 1

'get number of scan files
FirstS:
strFirstScan = InputBox("Enter first scan file number (4 digits)" & vbCrLf _
                       & "or enter 'Q' to quit", "First Scan")
'test that text is a four character string representing a number
blnRedo = False
If Len(strFirstScan) <> 4 Or Not IsNumeric(strFirstScan) Then
    If strFirstScan = "Q" Or strFirstScan = "q" Then
        Exit Sub
        Else
        blnRedo = True
    End If
End If
'redo first number entry
If blnRedo = True Then GoTo FirstS

SecondS:
strSecondScan = InputBox("Enter Second scan file number (4 digits)" & vbCrLf _
                        & "or enter 'Q' to quit", "Second Scan")
'test that text is a four character string representing a number
blnRedo = False
If Len(strSecondScan) <> 4 Or Not IsNumeric(strSecondScan) Then
    If strSecondScan = "Q" Or strSecondScan = "q" Then
        Exit Sub
        Else
        blnRedo = True
    End If
End If
'redo Second number entry
If blnRedo = True Then GoTo SecondS

'open each scan file in turn and copy information
For n = CInt(strFirstScan) To CInt(strSecondScan)
    'set destination column offset for first column (0 = "A")
    intDestColOffset = 0
    'create file name
    strThisFilename = strBase & Format(n, "0000") & ".xls"
    strThisFile = strPath & strThisFilename
    'open this file
    Application.Workbooks.Open (strThisFile)
    With Workbooks(strThisFilename)
        .Worksheets("Sheet1").Range("A1:B1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 2
        .Worksheets("Sheet2").Range("B1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1
        .Worksheets("Sheet2").Range("B2").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1
        .Worksheets("Sheet3").Range("A1:B1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 2
        .Worksheets("Sheet4").Range("B1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1
        .Worksheets("Sheet4").Range("B2").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1
        .Worksheets("Sheet5").Range("B1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1
        .Worksheets("Sheet6").Range("A1:A9").Copy
        Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset).PasteSpecial _
            Paste:=xlPasteAll, Transpose:=True
    End With
    'next row
   intDestRowOffset = intDestRowOffset + 1
   'close current source file
   Workbooks(strThisFilename).Close SaveChanges:=False
Next n

'save this Destination workbook
Workbooks(strDestFN).Save

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'reinstate screen updating
Application.ScreenUpdating = True
End Sub

You will see that screen updating is disabled during this macro to stop screen flicker during the copy and paste operations.

Hope this is what you were looking for or at least a starting point.

Regards


Report •

#6
March 9, 2010 at 01:30:18
Thank you, it must be a lot of work for you.

I really appreciate your work. there is one thing i dont get it.
I just copy and paste it, xls file names are scans_nummer, but it still doesnt work.

I used "Personal.xls" to try it, but still nothings changes.
I thought it maybe the location, i changed it back to
strPath = "C:\Temp\"


Could me help me agian please.

Thank you in advance.


Report •

#7
March 9, 2010 at 05:02:21
Hi,

Here are the steps to put the code into "Personal.xls":

Click Alt+f11 (the Alt key and function key #11 clicked together).
This opens the visual basic window.
On the left is the Project Explorer pane (if it's not visible click View on the Menu and Select 'Project Explorer'
In the listing, find VBAProject(PERSONAL.XLS)
Right-click it and select Insert... and choose Module (not Class module)
Double click the new module (Module1) (in the Modules folder)
In the large window on the right paste all the code.

Now change the following:
'setup Path to saved scan files - must end with \
strPath = "C:\Temp\"
The path is the folder containing your OmniPage-created scans.
The scans all start "scans_", so no need to change that.
Alt+f11 will take you back to the main Excel window.

On any toolbar in the main Excel window, right-click and select 'Customize'
In the 'Customize' dialog box that opens select 'Commands"
In the list on the left scroll down and click on Macros
Click the 'Custom Button' icon on the right, hold the mouse key down and drag the icon into position on a Toolbar.
Right click this new icon and click on 'Assign new Macro'
In the dialog box that opens find "PERSONAL.XLS!GetScans" and select it and click OK
Click Close in the Customize box.
(You can come back later and design a new image for your icon - right-click a toolbar, select Customize, right-click the new icon on the toolbar and select 'Edit Button Image...')

Now open your Destination workbook - the one that will contain the data from the selected scan files
This workbook must contain a worksheet named "Sheet1"
If this is a new workbook, use 'SaveAs' to name and save it.

Click your new button
The start and end input boxes will request the scan file numbers e.g. "0001" and "0008". Enter the leading zeros as appropriate.

A number of scan files will be opened, in this example "C:\Temp\scans_0001.xls" to "C:\Temp\scans_0008.xls"
All will be closed in turn, with the data appearing on Sheet1 of your destination workbook.

Let me know if this works, or if there are any problems.

Regards


Report •

#8
March 9, 2010 at 06:11:10
I have test it so far and everything is good.

This is an extra option, i want it in access so if I imported it in Access.

I need to empty space between each Anumber in Sheet6.

.Worksheets("Sheet6").Range("A1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 2
        .Worksheets("Sheet6").Range("B1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1
  .Worksheets("Sheet6").Range("C1").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 2
        .Worksheets("Sheet6").Range("A2").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1
  .Worksheets("Sheet6").Range("B2").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 2
        .Worksheets("Sheet6").Range("C2").Copy _
            Destination:=Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
            Offset(intDestRowOffset, intDestColOffset)
        intDestColOffset = intDestColOffset + 1

Is this the right way to do it? Or the smart way :+)


Report •

#9
March 9, 2010 at 07:28:37
Hi,

The only thing that I notice is that you are alternating
intDestColOffset = intDestColOffset + 1
and
intDestColOffset = intDestColOffset + 2

This means that only alternate Sheet6 cells are separated by an empty cell. Not sure if this is what you intended.

Glad that the basics are working. If you need to see what is happening, you can place a 'break point' in the code.

Next to the code on the left side is an empty space. If you click in this space, alongside a line containing code (not a comment), a dot will appear and the line will be shown in reverse video.

When you run the code, it will stop at this line. You can then use the f8 key to single step through the code.
Hovering the mouse of some of the variables will show their value, and in most cases you can swap back and forth to the main Excel window to see what is going on.

You can also use the Watch window to see what happens to more complex values.
Select and right click on:
Workbooks(strDestFN).Worksheets("Sheet1").Range("A1"). _
Offset(intDestRowOffset, intDestColOffset)
and select Add Watch...
The resulting box at the top will have this line in it, move to the right side and at the end add ".Address" (without the quotes - also note the period) and click OK.
The cell address calculated by the code will be displayed in a new window. This allows you to see what destination address is being created at each step.

Regards


Report •

#10
March 11, 2010 at 05:11:57
Well I have last qeustion Humar.

I was trying to get 2 empyt coloms between every valeua on Sheet6.

We are copying A1:A9. Example, pasted in new map.xls
A1 two empty coloms A2(form Sheet6) pasted in A4 in new map.xsl

A3 pasted in A7 in new map. The nummer of colom doenst matter I just need two coloms between every A1:A9 values for Sheet6.

Can you please help me out Humar.


Report •

#11
October 7, 2010 at 07:44:45
I have something similar and need help with.... I created a 2007 Excel MacroEnabled Workbook (which I named "Tracker") to open an excel file called “Combined.xls” that is a table I exported from Access 2007 and saved on my desktop.

BACKGROUND INFO:
I have a total of three worksheets in the “Tracker” workbook- Combined(sheet1), Report(sheet2), CASPR(sheet3)

For "Combined" (Sheet1) - Currently I have to manually copy the sheet from the exported Access 2007 "Combined.xls" file located on my desktop and paste into this sheet.

For "Report" (Sheet2) - I created a macro "FormatReport" - used to insert 4 rows at the top and add column headers and specific widths and makes it pretty and filterable for management.

For "CASPR"(Sheet3) - It has a macro "'FormatFormulas" - it is used for setting up sheet to export as a csv file to send to the state. This macro has to pull certain information from Combined (Sheet1)- and use certain formats, lengths and combining 2 columns into 1, etc.

Once I run the macros, I have to save the file as a MacroFree Workbook calling it "MT_Tracker 10072010" and send off to management to use in their Meetings and upload the CASPR sheet to edi. (the name of the workbook varies on the date – it uses the current date)

ISSUE:
After I go to Access 2007 and export the table “Combined.xls” - I need to create a macro in my “Tracker” workbook that will pull “Combined.xls” from my desktop into my current MacroEnabled workbook, so I can format it into 2 different worksheets.

I need this to be easier to do because management wants to pull the data themselves from Access 2007 and not have to do 3 or 4 different steps.

1. How do I get the "Combined.xls" to pull into "Tracker"??
2. Is there a way to export table from Access run a macro to pick up the file and perform the 2 internal macros save as macrofree workbook named "MT_Tracker (plus current date)" in 1 or 2 Steps?


Report •

Ask Question