For those of you who use VBA to code to databases

[lollytyg]

Here's a tip...

Some of the databases out there are not code monkey friendly and I for one hate typing all those field names over and over.

Use VBA to write the code for you...

Open SQL or Access and EXPORT to excel, select just one line, make sure you tell it only selected data. Pasted to the top of the worksheet, You should get the all the table headers in a straight horizontal line across the worksheet.

Put a button on the sheet, and in code mode, enter this:

<code>
Private Sub CommandButton1_Click()
For c = 1 To 20
Sheet1.Cells(c + 5, 2) = "Rst(""" & Sheet1.Cells(1, c) & """)=NewSheet(""Sheet1"").cells(NewRow," & LTrim(Str(c)) & ")"
Next c
End Sub
</code>

Your now vertical output on the sheet looks like this, and IS valid code:

<code>
Rst("ID")=NewSheet("Sheet1").cells(NewRow,1)
Rst("TraceID")=NewSheet("Sheet1").cells(NewRow,2)
Rst("FileType")=NewSheet("Sheet1").cells(NewRow,3)
Rst("FileDate")=NewSheet("Sheet1").cells(NewRow,4)
Rst("Action")=NewSheet("Sheet1").cells(NewRow,5)
Rst("Account_Name")=NewSheet("Sheet1").cells(NewRow,6)
Rst("Item_Norway_Type")=NewSheet("Sheet1").cells(NewRow,7)
Rst("Item_Tuskeegee_Type")=NewSheet("Sheet1").cells(NewRow,8)
Rst("MID")=NewSheet("Sheet1").cells(NewRow,9)
Rst("Item_Rondell_Type")=NewSheet("Sheet1").cells(NewRow,10)
Rst("E4_ID")=NewSheet("Sheet1").cells(NewRow,11)
Rst("Item_Description")=NewSheet("Sheet1").cells(NewRow,12)
Rst("Details")=NewSheet("Sheet1").cells(NewRow,13)
Rst("")=NewSheet("Sheet1").cells(NewRow,14)
Rst("")=NewSheet("Sheet1").cells(NewRow,15)
Rst("")=NewSheet("Sheet1").cells(NewRow,16)
Rst("")=NewSheet("Sheet1").cells(NewRow,17)
Rst("")=NewSheet("Sheet1").cells(NewRow,18)
Rst("")=NewSheet("Sheet1").cells(NewRow,19)
Rst("")=NewSheet("Sheet1").cells(NewRow,20)
</code>


Just copy and paste the lines you need after that pesky connection statement, and they're even spelled right!

cheers

lolly

 

[dhookom]

lolly,
Thanks for sharing. I think others might find this valuable or at least get the idea they can use code to automate writing code.

You should post your tips as "Tips" not "Question". This is an option available when creating a posting. Also, use TGML code tags which is as easy as formatting text in any word processor. TGML uses []s rather than <>s.

Private Sub CommandButton1_Click()
    For c = 1 To 20
       Sheet1.Cells(c + 5, 2) = "Rst(""" & Sheet1.Cells(1, c) & """)=NewSheet(""Sheet1"").cells(NewRow," & LTrim(Str(c)) & ")"
    Next c
End Sub

[COLOR=#4E9A06]' code to write code[/color]
    Rst("ID")=NewSheet("Sheet1").cells(NewRow,1)
    Rst("TraceID")=NewSheet("Sheet1").cells(NewRow,2)
    Rst("FileType")=NewSheet("Sheet1").cells(NewRow,3)
    Rst("FileDate")=NewSheet("Sheet1").cells(NewRow,4)
    Rst("Action")=NewSheet("Sheet1").cells(NewRow,5)
    Rst("Account_Name")=NewSheet("Sheet1").cells(NewRow,6)
    Rst("Item_Norway_Type")=NewSheet("Sheet1").cells(NewRow,7)
    Rst("Item_Tuskeegee_Type")=NewSheet("Sheet1").cells(NewRow,8)
    Rst("MID")=NewSheet("Sheet1").cells(NewRow,9)
    Rst("Item_Rondell_Type")=NewSheet("Sheet1").cells(NewRow,10)
    Rst("E4_ID")=NewSheet("Sheet1").cells(NewRow,11)
    Rst("Item_Description")=NewSheet("Sheet1").cells(NewRow,12)
    Rst("Details")=NewSheet("Sheet1").cells(NewRow,13)
    Rst("")=NewSheet("Sheet1").cells(NewRow,14)
    Rst("")=NewSheet("Sheet1").cells(NewRow,15)
    Rst("")=NewSheet("Sheet1").cells(NewRow,16)
    Rst("")=NewSheet("Sheet1").cells(NewRow,17)
    Rst("")=NewSheet("Sheet1").cells(NewRow,18)
    Rst("")=NewSheet("Sheet1").cells(NewRow,19)
    Rst("")=NewSheet("Sheet1").cells(NewRow,20)

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Hi,

Open SQL or Access and EXPORT to excel,

I'm having a bit of a problem. Could you explain how, if you are EXPORTING from Access or SQL Server (and that assumes that you are coding in one of those applications) how the Excel object references have no application object?

 

[lollytyg]

Skip,
thanx for the posting tips...

Sql or Access, open a table, select one line, external data, export to excel, structure and data, selected only, OK...

pops a blank excel ss, and populates with the headers, and how many ever lines you have selected.

This example assumes you will be coding in excel, to reach the data obj. I was coding in outlook, same vba... in mine inbound mail attachments are written to Access.

You export first just to get table names and spelling and a sheet just calling out for 3 lines of VBA...

The code above would be correct to edit or addnew a recordset obj named rst

You could reverse it, to read from an rst to a textbox or such...

This just shows how to use 3 lines to write MANY lines of directly pastable code...

 

[lollytyg]

Sry Dhookum,

Thank you for the forum tips...

Hope you guys can use it...

lolly

 

[SkipVought]

Have you ever used MS Query to IMPORT data from Access, SQL Server, Oracle, Excel, Text Files etc directly into Excel? No VBA required, although I often do code the query after importing a Recordset, but I COPY the SQL generated in the GUI.

Also, using ADO, via VBA, you can IMPORT tables without knowing the field names.

So thanks for posting this tip.

 

[lollytyg]

Just a snippit, dont wanna use it? np.

Yes I use queries, and occasionally the SSMS to talk to SQL, but that's just me.

The object here was to write to Access or SQL using ADO and VBA from an emailed, Zipped, CSV. In my case the VBA is doing a lot of validation on a per line basis, b4 it's written to the db, the reason it's not being imported. Works fine.

C ya.