Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 14th, 2011, 10:24 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

hI

Thank you so much you have been really really helpful.

I have it working with
Code:
.Offset(1, 2).Formula = "=COUNTA($A:$A)"
But its not bringing back a result it just says 0 when i know there are rows with text in the files its looking at
 
Old January 14th, 2011, 10:32 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

that's because
Code:
.Offset(1, 2).Formula = "=COUNTA($A:$A)"
is referring to the workbook that was active when you started running the code. So in the active sheet of the active workbork, put some text in there to see if it picks up that text.
Also, maybe you could try doing everything you need to do and then after the loop open Extract_Size and see if that works
 
Old January 14th, 2011, 10:44 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

hi

You;re right it is reference the active workbook. I put some text in column a the ExtractSizeChecker document and it picked it up straight away.

I have put the workbook its opening as a reference so the code looks like this
Code:
Sub Get_Dealer_Count()
Dim ws As Worksheet
Dim dd As Workbook
Dim xlCalc As XlCalculation
Dim strFldr As String
Dim strFile As String
Dim wb1 As Workbook

Application.ScreenUpdating = False
        
Set wb1 = Workbooks.Open("C:\Documents and Settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")

Set ws = wb1.Sheets("Dealer Extracts")

ws.Range("F17:H38").ClearContents
ws.Range("F17:H17").Value = [{"Directory", "Filename", "Row Number"}]

strFldr = ("C:\Production2\ATX\Extracts\201001\DealerData")

strFile = Dir(strFldr & "\*.csv*")

If Len(strFile) > 0 Then
    With Application
        xlCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    Do
        Set dd = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        With ws.Cells(ws.Rows.Count, "F").End(xlUp)
            .Offset(1).Value = strFldr
            .Offset(1, 1).Value = strFile
            .Offset(1, 2).Formula = "=COUNTA($A:$A)"
            .Value = .Value
        End With
        wb.Close False
        strFile = Dir
        Application.StatusBar = strFile
    Loop Until Len(strFile) = 0
End If

With ws.Range("F17:H17")
    .Font.Bold = False
    .EntireColumn.AutoFit
End With

End Sub
I tried to reference wb.sheets(1) in the forumla but it did not work and came up with the error from earlier
 
Old January 14th, 2011, 10:48 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

can you give me the specific code that's not working? It looks like you're missing the .name part of the wb.sheets(1).name
 
Old January 14th, 2011, 10:50 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Yep no problem.

the code thats not working is
Code:
 .Offset(1, 2).Formula = "=COUNTA($A:$A)"
I took out the .name bit of the code to see when it broke but when i put the .name bit in the error appeared again
 
Old January 14th, 2011, 10:53 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

but you said earlier that

Code:
 .Offset(1, 2).Formula = "=COUNTA($A:$A)"
does work and picks up the data in the active workbook.
 
Old January 14th, 2011, 10:58 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Sorry

Yes i meant that bit does work and picks up data from the extractsizechecker document. I tested it by placing text in column a in the extractsizechecker and ran and its picking the text up in there not the csv files from dealerdata.
 
Old January 14th, 2011, 11:22 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

ok so your next step is to continue to build that Counta formula. You know it works referring to the currently active workbook, manually open another workbook, put some data in the A:A range, then in your code set a reference to that manually opened workbook, then see if you can count the number of rows that have text in that manually opened workbook. Keep gradually building that formula up.

Also, what version of excel are you using? And help clear up the picture for me a bit. You have one workbook that is the one holding the code and that's the only one you've been running the code from correct? And that's the one where the data that you're extracting from is going into at least temporarily. What I don't understand is where the ExtractSizeChecker workbook comes in. I see nowhere in your code where you ever refer to it. You open it but never do anything with it. Why do you need it open? And if you need it open, why can't you open it after the looping procedure?
 
Old January 14th, 2011, 11:34 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Ok

I been able to get the code to got to
Code:
 .Offset(1, 2).Formula = "=COUNTA('Sheet1'!$A:$A)"
But then when i try to add
Code:
 .Offset(1, 2).Formula = "=COUNTA('["wb"]Sheet1'!$A:$A)"
it falls over

I am using 2010

Yes there is one workbook which is holding code, another workbook (extractsizechecker) which is where i want the results to go and teh folder with the files i want the forumula to be performed on.

Yes

Currently it is extracting data from extractsizechecker

It has gotton very confusing the code started with be able to put the results in the workbook which was holding the code but i was told i had to store the reuslts in another document and i have been trying to do that with no luck.

I hope that helps.
 
Old January 14th, 2011, 11:35 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Also i cant put it after the loop because the loop its self reference's ws which references wb1.......If that makes sense





Similar Threads
Thread Thread Starter Forum Replies Last Post
Run-time error '1004' with Named cells mahaneca Beginning VB 6 1 January 29th, 2009 01:04 AM
Run time error 1004 Ken Matthews Excel VBA 1 March 2nd, 2007 06:30 AM
Run-time error '1004' Document not saved Tucky Excel VBA 0 October 28th, 2004 12:04 AM
Run-time error '1004': raja.gogoi VB Components 1 July 18th, 2004 11:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.