Stumped...

[GBalcom]

I have to come up with a way to export specific fields of a query to specific areas of an excel spreadsheet. for instance, lets say I have 3 fields in my query; Labor Hrs, Material Cost, and Name.

Name needs to show up in column A of the spreadsheet. Labor hrs in column H, and Material Cost in column D. However, all the data for a given record needs to of course show up in the same row.

I'm really stumped on how to tackle this. This also leads me to wonder what a recordset really "holds"....(each specific field in a record, or each record)...

If each specific field is held on the move next, then maybe I could use a "select case" strategy for this, with the case being the field name?!?

Any ideas would be greatly appreciated.

 

[jdraw]

Recordsets contain records.

 

[Isskint]

have a look at the examples from Bob Larson at http://www.btabdevelopment.com/ts/default.aspx?PageId=10

There are 4 slightly different examples. They should show you the way

 

[mdlueck]

This also leads me to wonder what a recordset really "holds"....(each specific field in a record, or each record)...

ADO.RecordSet objects (and perhaps DAO,RecordSet objects as well) hold one or multiple records. All of the columns, for how ever many records. RecordSet objects differ from ADO.Parameters objects in that RecordSet objects are multi-record capable.

If each specific field is held on the move next, then maybe I could use a "select case" strategy for this, with the case being the field name?!?

You lost me here... CASE statements in SQL are a way to evaluate some business rules to obtain ONE value to be plugged into ONE field of the SQL Result Set which may end up in a RecordSet object.

As for bridging to Excel, there are many ways to accomplish that task. Could Export to an Excel file, then have the "master" Excel file perform a VLookup to the export / transfer sheet. That way Access could not mess up your "master" Excel file... at the worst it could only mess up the lookup file that the master sheet refers to.

 

[Rat1sully]

could you not just use a query to return only the fields you need and the copyrecordset method to get the data in excel then move it around to where you need it?

 

[mdlueck]

could you not just...

I was thinking of the possibility that a variable number of records might need to be sent to the spreadsheet, which might cause spreadsheet formatting errors.

We do not have enough details from the OP to tell.

Rat1sully, and no I was not taking what you wrote personally... more the bold line... I think it is time for the OP to reply as we are spinning our wheels with mental hallucinations.

 

[Rat1sully]

that's a fair point i keep finding myself saying that at work, almost always means i'm forgetting something obvious or need to go find more information

 

[jdraw]

Thanks for posting that Michael. I was thinking, we're thinking and answering and the OP is doing what exactly??? Time for him/her to provide some feedback and/or more info.
I'd also like to ask is this really an Access or an Excel issue -- is there rationale for both?
Could be, but we're only guessing .....

 

[GBalcom]

wow....I go into two meetings and come back to a frenzy


Rat1Sully,
I was thinking of that......there are only 3 specific queries that would need to be run, and because they are of the same general criteria, they should produce the same number of records. If I sort them some way, I think I could do this...

As far as the Select Case statement....I was brainstorming a way to say something like

If the Field.name = A, then do this, otherwise if Field.name = B, then that...etc. That is why I was asking what is contained in a recordset. Because if the recordset.movenext would move to each individual field in a record, then this might have worked (at least in my head).

If someone has any other questions, please let me know so I can answer and be more specific. I will try the 3 separate query route and post back with my results.

Thanks again for the help.

 

[GBalcom]

More issues

Hi guys,
I'm starting to see this is a bigger undertaking then I expected. I'm stuck on just the first part of it; creating a dialog box to browse to the place where the excel file to be modified is located. After two days of searching online for some code, and having a bunch of code not work due to object references? (said it needed Microsoft Object 12.0, but I only have 14.0 to choose?!?) Here is some code that seems to work. The only thing I now need is to set up a default location other than the desktop to start from. Can anyone point me in the right direction? I tried changing

' Set Default Root folder = Desktop
dirInfo.pidlRoot = 0&

to
' Set Default Root folder = Desktop
dirInfo.pidlRoot = "R:\vol2\Estimate\QUOTES"

but there is a type mismatch..

Anyways...here is the full code, which I have placed in a module:

Option Compare Database
Option Explicit

Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

'Here let us use the above shell functions to open the browse directory dialog

Public Sub Show_BrowseDirectory_Dialog()

' BrowseForFolder
' SHBrowseForFolder API Function Example

Dim dirInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer

' Set Default Root folder = Desktop
dirInfo.pidlRoot = 0&

dirInfo.lpszTitle = "Browse directory!"

' Type of directory
dirInfo.ulFlags = &H1

' Show the Browse Dialog
x = SHBrowseForFolder(dirInfo)

' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
MsgBox "You have selected :=" & Left(path, pos - 1)
Else
MsgBox "Browse a Directory..."
Show_BrowseDirectory_Dialog
End If

End Sub

 

[mdlueck]

That appears to be far uglier code than is necessary to obtain the File Dialog box. Please review the example here:

MS Office File Picker Dialog Box Example
http://www.access-programmers.co.uk/forums/showthread.php?t=232292#post1185165

You simply choose the correct bindings for the version of Office you have installed. I have not heard of these capabilities suddenly disappearing in Office 2010 (v14). My example code is from Office 2007 (v12).

 

[GBalcom]

I looked at Bob Larson's site and pulled some code off of it. (Excellent site Bob, many thanks)

What I'm thinking may work is to use the following code, and push an sql statement to it instead of the query name. But first I seem to be having a reference issue. Below is my Module Code:

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strFilePath is the name and path of the file you want to send this data into.

    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strPath As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler


    strPath = strFilePath


    Set rst = CurrentDb.OpenRecordset(strTQName)


    Set ApXL = CreateObject("Excel.Application")


    Set xlWBk = ApXL.Workbooks.Open(strPath)


    ApXL.Visible = True


    Set xlWSh = xlWBk.Worksheets(strSheetName)

    xlWSh.Activate




    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst



    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select


    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit


    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select


    rst.Close


    Set rst = Nothing


Exit_SendTQ2XLWbSheet:
    Exit Function


err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendTQ2XLWbSheet
End Function

Next is my Sub Code:

Private Sub cmdRunImport_Click()

    Dim strSql As String
    
    
    
On Error GoTo ActLogConversion

'Runs the margin Sheet Query
    'DoCmd.OpenQuery "qryMarginSheet", acViewNormal, acReadOnly

'Exports Information to Excel
    strSql = "Select [Description], [AggQty] From [qryMarginSheet] Order By [RecNo]"
    
    SendTQ2XLWbSheet "qryMarginSheet", "Sheet1", "C:\Users\gbalcom\Desktop\Export test.xls"


ActLogConversion_Exit:
    Exit Sub

ActLogConversion:
    MsgBox Error$
    Resume ActLogConversion_Exit

End Sub

___________________________

And attached are the error I'm recieving and a snippet of the references I have.



Thanks for any insight.

 

[mdlueck]

But first I seem to be having a reference issue. Below is my Module Code:

1) Please you code tags to wrap your source code. The [#] button just above the message area on your browser.

2) Turn on the debugger, perform stepped execution of the code, and mark in red the LOC which causes the error please.

 

[GBalcom]

Hi Mike,
I'm sorry, but I don't understand where you mean with the # sign next to the browser. Could you please explain?

I debugged and stepped through the program, it seems to be going to the error handling code after this line:

xlWSh.Range("A2").CopyFromRecordset rst

 

[mdlueck]

I'm sorry, but I don't understand where you mean with the # sign next to the browser. Could you please explain?

Where you type your messages... just above there is a button bar. Push the [#] to get the editor to insert the code tags, then insert code in-between them.

I debugged and stepped through the program, it seems to be going to the error handling code after this line:

xlWSh.Range("A2").CopyFromRecordset rst

My head aches trying to envision how that LOC would actually work.

I know others have posted Access VBA code which "types in cell values" into Excel, which I believe looks like what you are attempting to do. In my code I have preferred to use...

  'Export to an Excel file
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQryName, strCostedBOMFilename, True

to actually export in one fell swoop.

I just looked up the post of the "other method" I am recalling...

http://www.access-programmers.co.uk/forums/showthread.php?t=233104#post1190025

Perhaps that post of Bob's will help you.

 

[GBalcom]

Thanks everyone for the help in this. Unfortunately, as I dig deeper into this application, I'm realizing the reasoning behind this particular method does not add value to the overall process. I am now in the process of redesigning this and removing the spreadsheet from the process. I will let you know if I need any further help.

Thanks,
Gary