Access to Excel export with proper formatting 6

[djmousie]

The below code exports information for me from Access to Excel, and this works perfectly, however, I need for the export to properly format my excel sheets before the export (bolding, column sizes,etc.). Based on the coding below, what would I need to add to this in order for this to work?

Private Sub Command4_Click()
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT field1 FROM table ORDER By field1;")
strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strCrt = rs.Fields(0)
Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT table.* FROM table WHERE table.field1 = '" & strCrt & "';")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\file " & strCrt & ".xls", True
DoCmd.DeleteObject acQuery, "" & strCrt
rs.MoveNext
Loop
End Sub

 

[MichaelRed]

Access will / can not format the export information. It is possible to write Excel VBA code to format the cells in the target spreasheet. It is possible to write code in Ms. Access VBA to write the Excel VBA code to format the cells and to have the code exported tot he spreasheet. It is also possible to open the spreadsheet where the information was exported and format the cells from within Ms. Access using Ms. Access VBA code.

But you cannot get Ms. Access to format the information prior to / during the export process itself.




MichaelRed

 

[JerryKlmns]

djmousie,

If you export to an already formated but empty sheet of an excel file, all formats are kept on the sheet (except for column sizes autofit) as long as sheet name matches exported table/query name.

Assuming that rs records rarely change, create empty formated excel files with the same sheet names (all created queries are named as that name, so they are exported to the same named sheet). Before each export create a copy of that file using the filename you are about to export and then export.

ie
rs.Fields(0)="Koykoy"
empty excel file = "myKoykoy.xls"
formated sheet name "Koykoy"
copy that as "file Koykoy.xls" --> FileCopy "C:\myKoykoy.xls", "C:\file Koykoy.xls"
Export to "C:\file Koykoy.xls"

BTW
Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00") = Format(Date, "mmddyy")

and do add
rs.Close
Set rs = Nothing
Set db = Nothing
outside the loop

 

[djmousie]

The thing is, I have would have to create over 60 formatted excel sheets, with different file names since this is what I need to achieve. I'm sure there has to be a way for VBA to say OK, we've just created all these excel sheets, dumped all the proper information into each, now lets make the column width this size, and lets bold rows this and that...but it sounds like what MichealRed is saying above, it cannot be done...?

 

[MichaelRed]

Didn't say it couldn't be done, DID say you can't get the export function (or transfer... ) to do it.

Also didn't say it was a trivial exercise ... regardless of HOW you approach it, as it requires working with both versions of the object hierarchies/models and the variations in the application specific functions (e.g. object models) of VBA,




MichaelRed

 

[SkipVought]

Hi,

PULL the data from Access to Excel using MS Query.

Format as you wish, set the Data Range Properties to maintain formats (will adjust to the size of the querytable)

Then Copy that format to each other sheet, assuming the same format logic, and Edit the query as needed.

BTW, why 60 sheets?

Skip,
[sub]
[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors [/sub]

 

[djmousie]

Im bascially taking a master list of data, and what the query does is spit out a seperate sheet by vendor, and then I email each sheet to each vendor, so thats why I'm doing it that way. I guess what im trying to get at, is that I'm trying to save time but not having to reformat each sheet.

 

[JerryKlmns]

So that leads us back to one empty formated excel sheet???
You do keep in a table all vedors' data, don't you?

I would follow Skip's way....

 

[SkipVought]

You could do this with ONE SHEET and a combobox to select vendor. You would not need ANY code at all.

1) on sheet1 select DISTINCT vendor to make a vendor list. Name the range.

2) on sheet2...

a) use A1 as a Data/Validation - List to select a vendor using the named range.

b) select the data you need with a cirteria for a vendor. Link the parameter in A1 to the criteria in Data/Get External Data/Parameters

Skip,
[sub]
[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors [/sub]

 

[djmousie]

Skip,

What are your thoughts on the following code...I understand what you are trying to have me do above, it's sort-of what Id like to do, but I've gotten some feedback, and the below code is close, but I am still getting a run time error 3001 "Arugments are of the wrong type, are out of acceptable range, or are in conflict with one another" and the below code is highlighted....any thoughts as to why? I'm kinda a newbie to VBA so I'm slowly working thru my problems.

rs2.Open str1Sql, CurrentProject.Connection, acForwardOnly, acLockReadOnly, adCmdText

Private Sub Command5_Click()
 Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
     
    Dim oApp As Excel.Application
    Dim oWs As Excel.Worksheet
    Dim oWb As Excel.Workbook
     
     
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT DISTINCT vendor FROM billbacks ORDER By vendor;")
    strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
    rs.MoveLast
    rs.MoveFirst
     
    Set oApp = CreateObject("Excel.Application")
     
     
     
    Do While Not rs.EOF
        Set oApp = CreateObject("Excel.Application")
        Set oWb = oApp.Workbooks.Add
        Set oWs = oWb.Sheets(1)
        Dim rs2 As Recordset
        Dim y As Long, x As Long
         
        strCrt = rs.Fields(0)
         
        Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT billbacks.*  FROM billbacks WHERE billbacks.vendor = '" & strCrt & "';")
         
        Set rs2 = New Recordset
        rs2.Open str1Sql, CurrentProject.Connection, acForwardOnly, acLockReadOnly, adCmdText
        y = 1
        With oWs
            Do While Not rs2.EOF
                 
                For x = 1 To rs2.Fields.Count
                    .Cells(y, x) = rs2.Fields(x)
                Next x
                 
                y = y + 1
                 
                rs2.MoveNext
            Loop
            .Cells(1, 1).EntireRow.Font = Bold
            .Cells(1, 1).EntireRow.Interior.ColorIndex = 6
            .Cells.AutoFit
            .Cells(y + 1, 10) = WorksheetFunction.Sum(Range(Cells(2, 10), Cells(y, 10)))
        End With
        oWb.SaveAs {filename}
        oWb.Close
        Set oWb = Nothing
        Kill oWb
         
        rs.MoveNext
    Loop

End Sub

 

[SkipVought]

You don't need ANY code.

Skip,
[sub]
[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors [/sub]

 

[djmousie]

Skip, I am trying to avoid having to select each vendor since there are so many, I understand what your saying above, u would choose a vendor from the combo box and click on a command button, but I need something that will choose all vendors and create a spreadsheet for each vendor...I'm sorry if I'm being persistent, I'm just trying to better understand...

 

[SkipVought]

Then all you do, with some code, is progamatically write each vendor into the cell and then copy each resultset to a new workbook to send to the vendor. That's just a few lines of code.

If you want to use the above code, I'd suggest the CopyFromRecordset method rather than MoveNext in a loop.

Skip,
[sub]
[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors [/sub]

 

[pgoulet]

Did you ever get this project working. I recently got called back to active duty and am stuck in Iraq without most of my resources. For the most part I am not working in my career field, or even my old army MOS, which is probably a good thing LOL. I am trying to do something very similiar to what you were trying to accomplish. But like I said, previous code I wrote and my references are all back home.

I would apprciate a look at the code if that is possible.

Thanks again for your any help.

 

[djmousie]

Unfortunetly, I gave up on the project a while back due to other priorities. Like I said, the original code will spit items into excel from access, but WITHOUT the proper formatting. I've been to many other forums to get other opinions. Some have tried to help, but nothing quite worked the way I wanted to. Most people are telling me I shouldnt have to code anything, but I have a lot of data that I need to export to MANY excel files, so if there was a way to do it, it would make my life easier. Do a GOOGLE search with the something similar to the subject heading of this thread and also inlcude the word "Forum". Let me know if that helps, and if you, yourself finds an answer, please let me know as well.

Thanks

 

[pgoulet]

Hello djmousis,

You just about had it right all the way around. It only took a little bit of testing and some very minor changes to get it right. So, now you should be able to export data directly to the excel sheet and also multiple sheets. Just drop this code behind a button and away you go.
************************************
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT distinct fieldname FROM table (or query Probably without parameters) ORDER By fieldname;")
'**** fieldname, consider this to be how the records are grouped within the dataset.

rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
strCrt = rs.Fields(0)
Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT table.* FROM table WHERE table.fieldname = '" & strCrt & "';")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\filename " & ".xls", True
DoCmd.DeleteObject acQuery, "" & strCrt
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

********************************

Thank you again for all your help.

Peace Paul

 

[djmousie]

Yah I was able to get that to work, it's properly formatting the excel sheet (custum headers) and column widths and what not that I was also trying to add to the code. Since I have about 60 spreadsheets of data that exports, they all export unformatted. See what IM saying?

 

[pgoulet]

I am thinking that each worksheet is basically identical?

If they are, can you create a "Master" worksheet in a separate workbook, with all the formating complete.

1. You could consider calling a macro that would then select and copy the entire working MASTER worksheet.

2. Have the macro select the 1st worksheet and do a paste special formating only.

3. Itenerate through each worksheet and do the same thing.

Haven't done that kind of work in a long time. Just hoping to give you another approach to the issue.

Good luck.

 

[djmousie]

Well the problem with using a Macro with a master sheet is, that I have about 60 seperate workbooks that spit out once the query is finished. I'd have to open up each sheet individually to reformat, that is what I am trying to avoid.

Thanks again with the follow up and come home safe.

 

[pgoulet]

I agree you should not be opening the workbook. Just itenerate through each worksheet programmatically, then have the program do the paste special.

so..

Workbook a is the master.

your program spits out the 60 worksheets into a new workbook, b.

1. call a procedure that opens to the master worksheet and do a copy of the entire worksheet.

2. have the procedure then move to workbook b worksheet(x), select the entire worksheet and then do a paste special, formating.

3. have the procedure then move to the next worksheet and repeat 2 until there are no more worksheets.

Anyway, your post was so helpful, that I wanted to offer some ideas. I have done what I am trying to describe here, but I really don't have access to my reference materials, or previous code, at least for a few more months. If a master worksheet is not practical, then of course this idea won't work.

Good Luck.