VBA Query Export Formatting 1

[JPeters]

I'm exporting a query to excel from access and I'm running into the problem if Excel auto-formatting the appended records that I am exporting to an excel file. Basically it's reformating "General" formatted fields to "Time/Date". So when I want to see the number 1, or 94 - Excel creates a strange date taht corresponds to that number. Like 09/10/1899 .. something along those lines. When I change the format manually back to general format, all is well. I'd like to do thsi by code so the end user doesn't have to.

I've already gone into my excel template that is being opened and tried to set the column format through there, but no good - the code just reformats for me.

I've been scanning the excel object library and it looks like there is something in the Range category I can use. But I'm having trouble putting my finger on it. Here is my code, please feel free to make suggestions.

'***
Function appndXLS(strPath As String, strFilename As String, strOpenFilename As String, _
strSHName As String, qrySQL As String)

'Create an Excel Instance
Set myXl = CreateObject("Excel.Application")

'Check to see if the template file exists:
If fbol_Check_File_Name(strPath, strOpenFilename, ".xls") = False Then
MsgBox "Template file missing." & Chr(13) & "Please place in the appropriate directory.", _
vbExclamation, "File missing:"
Exit Function
End If

'Set your Excel File to push the data too
Set myBk = myXl.Workbooks.Open(strPath & "\" & strOpenFilename & ".xls")

With myBk.Sheets(strSHName)
'Set your range to first available cell in Column A
'Stack your Sql in a String
Set db = CurrentDb()

Set qdf = db.CreateQueryDef("")
With qdf
.sql = db.QueryDefs(qrySQL).sql
Set rs = .OpenRecordset()
If rs.RecordCount = 0 Then
MsgBox "No records were found.", vbOKOnly
myBk.Close True: Set myBk = Nothing
myXl.Quit: Set myXl = Nothing
rs.Close
db.Close
Exit Function
End If
rs.MoveFirst
End With
If Not rs.EOF Then
rs.MoveLast: rs.MoveFirst
'Rezise Excel Target Range Cell Array to
'Accomodate Recordset Height/Width
.[a2].Resize(rs.RecordCount + 1, 3).CopyFromRecordset rs
'I think the correction needs to go right here[/color red]
' If SysName = "CASPR" Or SysName = "Canc_Def" Then
'.[a1].Resize(rs.RecordCount + 1, 3).CopyFromRecordset rs
' End If
End If
rs.Close
Set rs = Nothing
db.Close
End With
'Check to see if file already exists.
If fbol_Check_File_Name(strPath, strFilename & Format(Date, "YYYY_MM_DD"), ".xls") = True Then
myBk.Save
Else
myBk.SaveAs FileName:=strPath & "\" & strFilename & Format(Date, "YYYY_MM_DD") & ".xls"
End If
'Close workbook, saving it, release WB object variable
myBk.Close True: Set myBk = Nothing
'Close Excel instance, release application object variable
myXl.Quit: Set myXl = Nothing
MsgBox "File has been saved to your" & vbCr & _
strPath & " directory."
End Function
'***

------------------
'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
joshua.peters@attws.com
------------------

 

[marina9]

One trick I learned from another thread is to export your file to Excel, open the spreadsheet, create a macro where you manually format the entire column (and perform any other formatting you would like, such as auto-fit, highlighting, etc.), stop the macro and review it's code. The programming is already done for you. All you need to do in insert the formatting code into the appropriate place in your Access VBA code. Hope this helps!

 

[JPeters]

marina9,
Thank you for the suggestion. Actually I have it working that way as we speak, but I'm trying to avoid any macros whatsoever. I'd like it all done through Access w/out opening the file until the end user is ready to look at it. So I really need to study up on Pivot Table programming and most importantly formatting programming.

-Josh

------------------
'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
joshua.peters@attws.com
------------------

 

[henniec]

marina9,

A couple of suggestions in formatting any column to what you want try the following.

.Columns("A").NumberFormat = "MMM dd, yyyy"
.Columns("A").ColumnWidth = 14
.Columns("B:G").ColumnWidth = 11
.Columns("A").HorizontalAlignment = xlCentre
.Columns("F").HorizontalAlignment = xlLeft

'Format cells
.Range("A1:W1").Font.Bold = True

' .Columns("E:G").NumberFormat = "##0.000"
.Cells(3, 7).Value = Format(myReadings!Ave, "##0.0")

.Columns("A:A").Select
.Selection.ColumnWidth = 9.2
.Selection.NumberFormat = "@" 'text formating
.Columns("A:N").HorizontalAlignment = xlCenter
.Columns("D:H").NumberFormat = "###0.00"
.Columns("L:N").NumberFormat = "###0.0"


These have been taken randomly from one of my projects.

Hope it works.

Run a macro and adapt the VB code in Access to your needs.

Hennie

 

[marina9]

JPeters,

Hennie has given you an excellent example of some format programming you can use in your application. Some additional tips:

To format an entire column to "General" via code, you can add

'format excel spreadsheet
With myXl 'your Excel application object
.Columns("B:B").Select
'or whatever range you want
'next line will format entire column to General
.Selection.NumberFormat = "General"
'bolds titles in first row
.Rows("1:1").Select
.Selection.Font.Bold = True
'autofits and left aligns the columns specified in range
.Columns("A:L").Select
.Selection.Columns.AutoFit
.Selection.HorizontalAlignment = xlLeft
'selects the first cell in spreadsheet
.Range("A1").Select
End With

If you place this code in your access module you won't have to include any macros in your excel spreadsheets. The macros was just a way to get the relevant programming logic without having to search the net for format programming tips.

 

[JPeters]

Thanks guys,
I ended up looking around on MSDN and found all of the possibilities for NumberFormat. I appreciate all the help guys. You pointed me in the right direction. Actually, you ended up giving me exactly what I needed in the end. Thanks again.

-Josh

------------------
'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
joshua.peters@attws.com
------------------

 

[marina9]

Glad we could help!

 

[SkipVought]

Hi,

Basically it's reformating "General" formatted fields to "Time/Date". So when I want to see the number 1, or 94

Excel is "smart" (has some code) to recognize Date/Time formats as defined by your regional settings. So 1-94 is ASSUMED to be a DATE by Excel. Eacel parses the STRING that you enter and generates a DATE (1/1/1994 is REALLY 34335) Once Excel assumes that the column is a date, then EVERY value is thereby converted or assumed to already be a date. So 1 becomes 1/1/1900 and 94 becomes 4/3/1900, the first and ninty fourth day respectively, starting with 1/1/1900.

I would set up the QUERY in Excel via Data/Get External Data/New Query - Access Files - Your Database - Your Table - [Next], [Next], [Next], [Finsh] (If you want to edit the query do it here via the option button choice)

You can format to refresh each time the workbook is opened. NO HASSLE!

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[JPeters]

Once again, great advice. I had thought about doing that. But I managed to get it working another way - if it's not broken, don't fix it.

However! -> Here's a Q for you. With this project I'm actually exporting 3 queries into 3 separate excel worksheets under 1 workbook. I have a total of 5 worksheets in the book. Two of them are pivot tables built off of 2 of the 3 exports, these pivot tables need to be updated to account for new lines that were added to the data source from access. And before I can update the queries, I'm required to run a macro that does a vlookup among other things and fills in a new column.

The last one is the 3rd export from access and I need this to be subtotaled up based on Date with 2 different field counts selected.

Long and the short of it is, this is a headache. I really dislike using excel for formatting, and I even more so dislike writing excel Macros when I'd prefer to just call the code from access during the export. All I want in Excel is a neat little report ready for my boss.

I am currently doing this all manually, but I was wondering what the Access VBA function/code was for subtotals, pivot tables, VLookups.. and how much of a hassle is it? It only takes about 5 minutes and the report is ran once a week, so it's not a HUGE deal. But I'm good at learning code, and I bet I could bang it out in under an hour if I knew what the basic code was... those 5 minutes a week can add up. Why do it manually when you can AUTOMATE!! ;-)

------------------
'How to Keep Your Databases from becoming Overwhelming!' thread181-293590
joshua.peters@attws.com
------------------

 

[SkipVought]

Your 3 sheets with query results can each be refrenced by a DYNAMIC NAMED FORMULA that in turn can be referenced in each PivotTable's Source Data definition.

The DYNAMIC NAMED FORMULA is of the form...
[tt]
=offset(sheet1!$A$1,0,0,counta(Sheet1!$A:$A),counta(sheet1!$1:$1))
[/tt]
This is defined and named in Insert/Name/Define

This takes care of the variable amount of data that each PT will reference from load to load.

What are you using the VLOOKUP for? Cances are this could be "automated"

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[JPeters]

I thought I had already posted this, it must have not gone through:

The VLookup is basically checking another excel book for a 1 to 1 value, when it finds this value (Say matching Project#), it runs some code that populate a field with a hard coded value per the match.

I was wondering if any of you knew how to program a macro to run in Excel on open of the file... or on data change? .. something like that. Cuz I found out all the coding for my Vlookups, Pivot tables, and subtotals ... but even with the Excel object library referenced in Access, I can't get it to do this from my VBA module. So I was thinking that once all the data is in the report, they just need to open it up and it will refresh and format itself to the appropriate view. Is it anything like Access' Form OnOpen?

Thanks again,
Josh

------------------
'How to Keep Your Databases from becoming Overwhelming!' thread181-293590
joshua.peters@attws.com
------------------

 

[SkipVought]

More than likely, the results of your VLOOKUP could also be done with a Query from Excel to another Workbook. All the queries could be refreshed on the Workbook Open Event.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[JPeters]

Skip,
I sort of got thrown into Excel and I'm learning it sort of based off of my Access knowledge. The learning curve isn't nearly as steep, but it's still slow goings - considering I'm working on about 20 projects at once all in different areas. I devote as much time as I can spare to learning the intricate workings of Excel. I'm assuming if we do the Workbook Open Event, the user will be required to enable macros? Is there anyway I can do this from Access. I mean I've got the Excel workbook and worksheet opened as objects... The Object library for excel referenced. I don't see why I need to bring Excel into the mix. However, I'm just having trouble getting Access to order the excel spreadsheet around - past it's inherent abilities. Getting down to Excel specific stuff (like pivot tables) is a bit nitty gritty. Could you recommend a FAQ or article... or even a book that might be helpful? I've been doing some research and am only finding other sites similar to Tek-Tips.

-Josh

------------------
'How to Keep Your Databases from becoming Overwhelming!' thread181-293590
joshua.peters@attws.com
------------------