Dave
12/19/2006 4:28:00 AM
Martin,
The code runs without any errors but isn't returning any data. The screen
flashes, but the sheet I want to the data is blank. Here is the code: (I am
not worrying about the IF statements at the moment. My first priority is to
be able to consolidate all of the information other workbooks into on
spreadsheet. I don't want to take up any more of your time worrying about IF
statements.)
Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\Documents and Settings\David Morris\Desktop\Test"
' prefix for the file name
Const cszFilePrefix As String = ""
' extension for the file name
Const cszFileExtension As String = ".xls"
Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String
Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur <> ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data >>>
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("1:1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("2:2")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("3:3")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub
"Martin Fishlock" wrote:
> Dave:
>
> Q. Where do I instert the "IF" criteria statements in the code?
>
> A. After the rem statement 'get data.
>
> Q. Do I have to insert the name of every workbook in the folder into the
> code?
>
> If you set the specifications at the start of the macro it should work on
> all files.
>
> Const cszDir As String = "C:\data\" this is the directory
> ' prefix for the file name
> Const cszFilePrefix As String = "" ' set this to "" as you have no prefix.
> ' extension for the file name
> Const cszFileExtension As String = ".xls" ' this is the extension and you
> say .xlc not .xls ????
>
> It will give you a headache because the file names are in mm.dd.yy format
> and not in yy.mm.dd which would order it correctly. So you many have to sort
> the data afterwards.
>
> > The workbook names are actually dates (i.e. 12.1.06.xlc and so forth). Will
> > this code look through every book in the specified folder and return the
> > specified information regardless of it's name?
>
> --
> Hope this helps
> Martin Fishlock
> Please do not forget to rate this reply.
>
>
> "Dave" wrote:
>
> > Martin,
> >
> > Thank you so much. This is starting to come together perfectly. The code
> > apears to work, but I think the tweaks I made are causing problems.
> >
> > Where do I instert the "IF" criteria statements in the code?
> >
> > Do I have to insert the name of every workbook in the folder into the code?
> > The workbook names are actually dates (i.e. 12.1.06.xlc and so forth). Will
> > this code look through every book in the specified folder and return the
> > specified information regardless of it's name?
> >
> > I am sorry to keep bothering, but I am almost there. I have learned more
> > about VBA in the past ten minute than I have in the past three days of
> > working on this. Thanks.
> >
> > "Martin Fishlock" wrote:
> >
> > > My posts seem to have got a little messed up....and there seems to bew two
> > > answers to the original question, never mind they are both the same.
> > >
> > > In answer to your questions
> > >
> > > Q: Is there a way to specify criteria using an array? (i.e. if row 10="one",
> > > then only pull information from rows 1-9 and put that information in the new
> > > workbook.) If this is too difficult it is not absolutely necessary, though
> > > would be extremely helpful.
> > >
> > > A: Yes it is. You don't need to use arrays because it then gets difficult to
> > > control the output but you would deal with the different treatments in this
> > > type of manner:
> > >
> > > if wbc.Worksheets(1).Range("A1") = "Widgets" then
> > > wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
> > > elseif wbc.Worksheets(1).Range("A1") = "Acme" then
> > > wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
> > > elseif wbc.Worksheets(1).Range("B1") = "Gold" then
> > > wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
> > > '.....
> > > endif
> > >
> > > ie testing the conditions and then dealing with the specific data.
> > >
> > > Where do I paste this code once it is complete?
> > >
> > > You paste the code into a module in the visual basic editior.
> > >
> > > 1. Tools|Macros|Visual Basic Editor or Alt+F11
> > > 2. In the edotor insert a module file.
> > > you needt to ensure that the project explorer window is displayed
> > > (View|Project Explorer) and then select the workbook that you are using.
> > > 3.Then insert a module file (Insert|Module)
> > > 4. Paste the code in the module window.
> > > 5. Tweek the code as required
> > > 6. Check it (Debug|Compile VBA Project)
> > > 7. Go back to excel workbook
> > > 8. Run the macro (Tools|Macros) and select the macro and run it.
> > > 9. Hopefully it should work.
> > >
> > > Is there a way to create a button that when pressed will update this new
> > > workbook?
> > >
> > > It is possible to create a button and one way to do this is with the forms
> > > toolbar.
> > >
> > > With the 'button' button on the forms toolbar select it and then draw the
> > > button on the worksheet and them assign the macro to it.
> > >
> > > Note in this situation you need to modify the code and before
> > >
> > > Set wsd = ActiveSheet
> > >
> > > you need to insert
> > >
> > > workbooks.add
> > >
> > > to make a new workbook.
> > >
> > > --
> > > Hope this helps
> > > Martin Fishlock
> > > Please do not forget to rate this reply.
> > >
> > >
> > > "Dave" wrote:
> > >
> > > > Marin,
> > > >
> > > > This looks great. A few more questions:
> > > >
> > > > I am new to VBA and Macros.
> > > >
> > > > Is there a way to specify criteria using an array? (i.e. if row 10="one",
> > > > then only pull information from rows 1-9 and put that information in the new
> > > > workbook.) If this is too difficult it is not absolutely necessary, though
> > > > would be extremely helpful.
> > > >
> > > > Where do I paste this code once it is complete?
> > > >
> > > > Is there a way to create a button that when pressed will update this new
> > > > workbook?
> > > >
> > > > I really do appreciate your help.
> > > >
> > > > "Martin Fishlock" wrote:
> > > >
> > > > > Dave, the following will do as requested.
> > > > >
> > > > > You may need to tweek the dir function or consider just a loop if you have a
> > > > > fixed file name structure (ie week01.xls, week02.xls....).
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Sub getdata()
> > > > > ' directory where the data is with the trailing \
> > > > > Const cszDir As String = "C:\data\"
> > > > > ' prefix for the file name
> > > > > Const cszFilePrefix As String = "file"
> > > > > ' extension for the file name
> > > > > Const cszFileExtension As String = ".xls"
> > > > >
> > > > > Dim wsd As Worksheet
> > > > > Dim wbc As Workbook
> > > > > Dim lRowDst As Long
> > > > > Dim szFileCur As String
> > > > > Dim szDir As String
> > > > >
> > > > > Set wsd = ActiveSheet
> > > > > lRowDst = 2
> > > > > szFileCur = Dir(cszDir & cszFilePrefix & _
> > > > > "*" & cszFileExtension)
> > > > > Do While szFileCur <> ""
> > > > > Set wbc = Workbooks.Open(szFileCur, , True)
> > > > > 'get data'
> > > > > wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
> > > > > wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
> > > > > wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
> > > > > '....
> > > > > wbc.Close False
> > > > > szFileCur = Dir
> > > > > lRowDst = lRowDst + 1
> > > > > Loop
> > > > > End Sub
> > > > >
> > > > > --
> > > > > Hope this helps
> > > > > Martin Fishlock
> > > > > Please do not forget to rate this reply.
> > > > >
> > > > >
> > > > > "Dave" wrote:
> > > > >
> > > > > > I need to find a solution to the folowing problem:
> > > > > >
> > > > > > There is a network folder with an Exel workbook for every week in the year.
> > > > > > Each Friday it is updated with one new sheet for that week. All the data
> > > > > > contained is in the same format. I need to create a single file that when
> > > > > > one presses a button, will go through all of the workbooks in the network
> > > > > > drive and pull specific information and populate it into the one file. Is
> > > > > > this possible? Any help would be appreciated.