Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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, 08:59 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default opening a workbook in a macro

Hi

I have this piece of code which works fine however i need to open a workbook first before it runs the rest of the code.

Code:
 Sub Get_Dealer_Count()
Dim ws As Worksheet
Dim wb As Workbook
Dim wb1 As Workbook
Dim xlCalc As XlCalculation
Dim strFldr As String
Dim strFile As String

Application.ScreenUpdating = False
        
Set ws = 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 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('[" & wb.Name & "]" & wb.Sheets(1).Name & "'!$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
Does anyone know how?

Thanks

Jeskit

Last edited by jeskit; January 25th, 2011 at 05:21 AM.. Reason: Solved
 
Old January 18th, 2011, 10:53 AM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

How to open a workbook? You've already got code to do that:

Code:
Workbooks.Open("name of workbook.xls")
Otherwise, can you be more specific?
__________________
Regards,
JP
JP SoftTech





Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible to run macro from another workbook? rmilward Excel VBA 2 September 26th, 2009 08:00 PM
on opening workbook, how to disable macro ? jjk2 Beginning VB 6 1 January 7th, 2008 05:05 AM
Generate A Workbook With Macro Included robnot Excel VBA 1 February 16th, 2007 07:08 PM
personal macro workbook helmekki Excel VBA 0 May 31st, 2004 03:11 PM





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