Need some guidance about Excel please...

[Tcs]

I haven't actually played with coding any VBA to make anything happen regarding Excel. (I'm running
2k3, as part of Office 2k3.) So while I know VBA (not as an expert mind you), I'm starting from
scratch when it comes to dealing with Excel, regardless of whether we're talking workBOOK,
workSHEET, or whatever.

I was hoping that perhaps someone with more experience than I might provide some advice as to
whether what I'd like to do is even possible. And if so, maybe how.

I have a table of some 22,000 records (currently). These records are all the Applications which
were made for Building permits of one kind or another. The timeframe is from January 2000 thru
August 2006 (the present). Each record contains the year it was created, along with other pertinent
data, including a calculation of how many days it took to process (be issued a permit from the date
of application). This processing time is our primary interest.

What I would *like* to do, is create one workbook with multiple worksheets inside. For example:

1.) Overall stats: Average processing time & counts for ALL permits (and by type).
2.) Stats by year: Average processing time & counts for ALL permits (and by type) for a specified
year.

There are probably others, which don't come to mind at the moment.

Normally, I would generate a query for each of the sheets I'm after. But I can't use the same name
over again, as Excel wants to replace the existing book with my current export. So I was thinking
that perhaps I could CODE what I want, and end up with everything pertinent to permits in JUST ONE
WORKBOOK.

Possible? No? Easy? Hard? How? Stick with doing it manually?

Thanks in advance for your guidance,

Tom

 

[Guest]

It may be possible to do this using the TransferSpreadsheet method. Although
VBA Help says using the Range argument if you are exporting will fail, that
is incorrect. Specifiy the name of the worksheet you want to create within
the Excel file.

It is possible using Automation to manipulate an Excel Spreadsheet, it takes
a lot more coding and has the potential to create problems if not done
correctly.

 

[fredg]

I haven't actually played with coding any VBA to make anything happen regarding Excel. (I'm running
2k3, as part of Office 2k3.) So while I know VBA (not as an expert mind you), I'm starting from
scratch when it comes to dealing with Excel, regardless of whether we're talking workBOOK,
workSHEET, or whatever.

I was hoping that perhaps someone with more experience than I might provide some advice as to
whether what I'd like to do is even possible. And if so, maybe how.

I have a table of some 22,000 records (currently). These records are all the Applications which
were made for Building permits of one kind or another. The timeframe is from January 2000 thru
August 2006 (the present). Each record contains the year it was created, along with other pertinent
data, including a calculation of how many days it took to process (be issued a permit from the date
of application). This processing time is our primary interest.

What I would *like* to do, is create one workbook with multiple worksheets inside. For example:

1.) Overall stats: Average processing time & counts for ALL permits (and by type).
2.) Stats by year: Average processing time & counts for ALL permits (and by type) for a specified
year.

There are probably others, which don't come to mind at the moment.

Normally, I would generate a query for each of the sheets I'm after. But I can't use the same name
over again, as Excel wants to replace the existing book with my current export. So I was thinking
that perhaps I could CODE what I want, and end up with everything pertinent to permits in JUST ONE
WORKBOOK.

Possible? No? Easy? Hard? How? Stick with doing it manually?

Thanks in advance for your guidance,

Tom

You have posted this message to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.
Please repost to the Excel newsgroup for whatever version of Office
program you are using.

 

[chris.nebinger]

Possible.
Medium difficulty (made easier by the fact that you can record macros
in Excel)

(Nevermind the person redirecting you to Excel, as I'm sure they would
redirect you here)


First, create a reference to the Excel object model. In the IDE, click
on Tools, References, and find Microsoft Excel reference.

Now, you are ready to start:

I would look here:

http://www.databasejournal.com/features/msaccess/article.php/3563671

And remember, if you record a macro, you can then look at the code and
figure out how Excel is performing actions, so you can duplicate it.

And, if you get stuck on a specific portion, be sure to ask questions
here.


Chris Nebinger

 

[Guest]

As long as you know how to fill the seperate work books ( maybe by year of
activity?). Do that and then use this code to consolidate workbooks into one
workbook with each prior workbook appearing on one worksheet. This is from
the Microsoft worksite.

Take this one

Sub TestFile3()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
SaveDriveDir = CurDir
MyPath = "G:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub