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, 11:46 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

ok well that's not correct syntax:
Code:
 .Offset(1, 2).Formula = "=COUNTA('["wb"]Sheet1'!$A:$A)"
this is:
Code:
 .Offset(1, 2).Formula = "=COUNTA('[" & wb.name & "]Sheet1'!$A:$A)"
Also, ws is NOT referring to ExtractSize, it is referring to the sheet "Dealer Extracts" in the CURRENTLY ACTIVE workbook. If you want it to refer to the ExtractSize workbook, you must FULLY qualify that:
Code:
'not
Set ws = Sheets("Dealer Extracts")
'but
Set ws = wb1.Sheets("Dealer Extracts")
 
Old January 14th, 2011, 11:53 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Ok

I ahve tried that syntax and it doesn't work.....It stops working as soon as the [" & wb.name & "] gets put in.

I have got Extractsizechecker in reference so it is:
Code:
 
Set wb1 = Workbooks.Open("C:\Documents and Settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls")

Set ws = wb1.Sheets("Dealer Extracts")
ws is then referenced in the loop
Code:
 ws.Cells(ws.Rows.Count, "F").End(xlUp)
to place the results in extractsizechecker (well thats would it should do)

What i would like is the workbook holding macro to be ignored and the macro to open extractsizechecker, Open the folder location run the loop on the csv files in that folder and place the results of that forumla in Extractsizechecker.
 
Old January 14th, 2011, 11:54 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

another approach you can take is just hardcode the name of the first workbook that gets opened in the loop and see if that works:
Code:
.Offset(1, 2).Formula = "=COUNTA('[myCsvFile.csv].mySheetName'!$A:$A)"
it will keep putting data from the first file opened, but see if it works that way
 
Old January 14th, 2011, 11:56 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

ok well i noticed that you've switched the code, originally it was wb, then you switched to wb1 and dd, so can you give me all the code that you have so we're on the same page?
 
Old January 14th, 2011, 12:02 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

give me a little while, i'm going to put together a simple program that i know works with what you have given me. It will open one csv file, open ExtractSize, and put data in ExtractSize which is the count of rows that have text in them in the csv file, does that sound about right?
 
Old January 14th, 2011, 12:03 PM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Ok i tried that and i got the same error as before.

I was thinking of your suggestion earlier to open the extractsizechecker after the loop.

Is there to change the loop so that ws.cells bit could be done afterwards so that it did the loop stored the results in a variable, then opened the extractsizechecker and added the values.

[code] If Len(strFile) > 0 Then
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
End With
Do
Set wb = 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('[Actual_Curr_Year_AT30010.csv].Actual_Curr_Year_AT30010'!$A:$A)"
.Value = .Value
End With
wb.Close False
strFile = Dir
Application.StatusBar = strFile
Loop Until Len(strFile) = 0
End If
 
Old January 14th, 2011, 12:52 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

ok i have three files:
masterBook, this holds the macros
ExtractSize, this holds the data
myCsvFile, which we're getting the number or rows from

The code sits in masterBook, opens ExtractSize, then opens myCsvFile in the loop and finds the number of rows in it and puts that number in the ExtractSize workbook. I ran it on my computer, it works
Code:
Sub Open_Csv_And_ExtractSize_CountRows()

Dim i As Integer 'for looping

'relevant workbooks
Dim wbExtractSize As Workbook
Dim wbCsv As Workbook

'relevant sheets
Dim wsDealerExtracts As Worksheet
Dim wsMyCsvSheet As Worksheet

'this will hold the next row to put data into
Dim lNextRow As Long

'set the workbook and worksheet
Set wbExtractSize = Workbooks.Open("C:\Users\Mike\Documents\deleteme\Extractsize.xlsm")
Set wsDealerExtracts = wbExtractSize.Sheets("DealerExtracts")

'find the next row available in ExtractSize, add two to it because there is a blank row at the top and you want the next available row
lNextRow = WorksheetFunction.CountA(wsDealerExtracts.Range("A:A")) + 2

'Loop through the csv files (in this case only one)
For i = 1 To 1
    Set wbCsv = Workbooks.Open("C:\Users\Mike\Documents\deleteme\myCsvFile.csv")
    Set wsMyCsvSheet = wbCsv.Sheets(1)
    With wsDealerExtracts
        .Cells(lNextRow, 1) = WorksheetFunction.CountA(wsMyCsvSheet.Range("A:A"))
    End With
    
    'increment to the next row
    lNextRow = lNextRow + 1
    
    'close it
    wbCsv.Close
Next i

wbExtractSize.Save
'wbExtractSize.Close

'clean up
Set wbExtractSize = Nothing
Set wbCsv = Nothing
Set wsDealerExtracts = Nothing
Set wsMyCsvSheet = Nothing

End Sub
As you can see, they all reside in the same folder on my computer. Set something like this up on your computer and this code should work for you. The one thing you will need to change is the path name and maybe the file extension for the ExtractSize workbook, i think it's "xls" for some versions of excel and others for other versions.

Hope this helps
 
Old January 14th, 2011, 12:59 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

forgot to mention a couple of things. In the ExtractSize workbook there is a sheet called DealerExtracts and on that sheet there is only one column of data in column A that looks like this:

<<<Blank Row>>
Data
1
2
3

In the csv file there are two lines of text that look like this:
hello, how, are, you
i'm, ok, how, are, you?

And that should pretty much work for you, lemme know.
 
Old January 14th, 2011, 01:10 PM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

HI

Thats absoulty fantasic however i am not sure understood what i meant.

What i have is:

- test1 = which is the excel document that holds the macro

- Extractsize = which is th excel doucment that the results need to be stored (C:\Documents and Settings\SeymourJ\My Documents\Extract_Size_Checker_test.xls)

- Then there is the dealer data folder (C:\Production2\ATX\Extracts\201001\DealerData). this folder holds multiple csv files which i need open, peform the counta and close

I just tried to change the code you gave me but i kept getting Type mismatch. I am sorry its so annoying.
 
Old January 14th, 2011, 01:13 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

lol, welcome to the world of programming. Can you give me the COMPLETE code you have?





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.