[RESOLVED] Export flexgrid to SQL

[tibor]

Hi all.
I have been messing around with Excel and VB. Now I have the Excel imported over to a flexgrid. It took alot of frustration for something simple but with help from si_the_geek, we were able to make it all happen. Thanks mucho. Now I was wondering if it was possible to take this information and send it to a SQL Server database table. What would be the best way to approach it? Or if possible to take it directly from Excel to the database. But I prefer to have the flexgrid just so i can see everything in VB before it goes. I have no code yet nor really asking for code at this point. Just a kick in the right direction or a tutorial someone may have laying around would be great for now. thanks alot.

tibor

[Al42]

Read the data into an array, populate the Flexgrid and fill the database table from the array (I do it this way in a couple of programs) or read the Flexgrid and send the data to the table.

Either way is valid.

[si_the_geek]

(for those of you who don't know it, the code used to fill a Flexgrid is here).

Al42, I'm afraid I dont see the purpose of an array as you described - it seems like a wasted step (but I may have read it wrong!).


Anyway, back to the question.. The main problem you have is in identifying the rows in the grid that have been edited (or added/deleted), and refering them back to the rows in the database.

To do this you will need to keep track somehow of the rows edited/added/removed. Presumably you will be using code at each point of the process (as the Flexgrid does not allow editing by default), so you can store the relevant information - possibly in arrays (or collections, or ...). When you want to update the database, refer to these to find out which rows you need to work with.

To ensure you are working with the right rows in the database, I would recommend changing the "fill" code, so that it also sets the RowData property for each row - using a unique (numerical) field in the database table. When you are doing the actual writing to the database, retrieve this value and use it as appropriate in Update/Insert/Delete SQL statements.

[tibor]

could i do it this way. with the flexgrid, just shut everything down that used excel objects and just use the flexgrid to show the data. then just use excel as a database object and run sql statements to insert and update?

[tibor]

And on a side note I am having a problem with there still being an instance of Excel running even after i set these

Set oXLSheet = Nothing
Set oXLBook = Nothing
Set oXLApp = Nothing

How would i go about ridding this after everytime i run my program?

[si_the_geek]

Setting the objects to Nothing doesn't close Excel - it just removes your link (which you need to do too). See my Excel Tutorial for the method to close Excel (post #5), and some useful tips on making sure that Excel closes properly (post 11).

could i do it this way. with the flexgrid, just shut everything down that used excel objects and just use the flexgrid to show the data. then just use excel as a database object and run sql statements to insert and update?

Is Excel your current "database"? If so, that is basically what I was suggesting.

[tibor]

Ok sorry i misread your post. I think that is the approach i will try. Results to come...

[tibor]

Wow cant believe i missed that in the tutuorial. We are getting closer now, lol

[tibor]

Ok ive ran into a bit of a problem. apparently my tables are going to be too big for a flexgrid. so i tried setting oXLApp.Visible = True so i can have the spreadsheet open instead of a flexgrid but it is telling me object required.

[si_the_geek]

apparently my tables are going to be too big for a flexgrid

That's news to me.. what is the problem/error here?

i tried setting oXLApp.Visible = True so i can have the spreadsheet open instead of a flexgrid but it is telling me object required

Presumably you have the scope wrong - you have declared oXLApp is a different sub to where you are using it.

[tibor]

it gives me a error of

unable to allocate memory for flexgrid

so i assumed it meant that it was too much data

i have oXPApp declared as an object

[si_the_geek]

Hmmm.. it sounds like you are right.

i have oXPApp declared as an object

The important thing is where is it declared, and where are you trying to use it?

Presumably the declaration (and inital use) is in one sub, and this code is in another.. am I right?

[tibor]

it is declared as a public in a module. it is being used on in 2 possible areas depending on what is selected. but both selections lead to the same code, just one is being pulled from SQL to Excel then at one time to the flexgrid. and the other is just any Excel file.

[si_the_geek]

The "object required" error is telling you that the object variable (oXLApp) is not valid at that point - the usual cause (on the forums at least) is that the object variable is not declared in the relevant scope, or has been re-declared in a second scope (eg: within the sub, when it is also declared in a module).

The other cause is that the code is being run before/after the object is valid (eg: before the first "Set oXLApp" line, or after the "oXLApp.Close")

[tibor]

hmm thats just wierd i sifted through the code and everything seemed to be fine and i tried placing that statement in different areas and still got the error.

[si_the_geek]

In that case you'd better upload the files, so that I can find out what the problem is.

[tibor]

ok heres the code in its entirety


Main Page

Option Explicit
 
Private Sub cmdLoad_Click()
Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.workbooks.Open(Dir1.Path & "\" & (File1))
Set oXLSheet = oXLBook.Worksheets(1)
 
vArray = oXLSheet.UsedRange.Value
 
  Set oXLSheet = Nothing
  Set oXLBook = Nothing
  
  
ExceltoSQLSub.Show
Unload Me
End Sub
 
 
 
Private Sub cmdMasLoad_Click()
 
Dim SQL As String
Dim Rs As New Recordset
cmdMasLoad.Enabled = False
cmdMasLoad.Caption = "Wait"
Call Connect2
 
         SQL = "SELECT * FROM tpa00175 "
         Set Rs = New Recordset
         Rs.Open SQL, oConn, adOpenDynamic, adLockOptimistic
         Set oXLApp = CreateObject("Excel.Application")
         Set oXLApp.Visible = True
         Set oXLBook = oXLApp.workbooks.Open(App.Path & "\Project.xls")
         Set oXLSheet = oXLBook.Worksheets(1)
         oXLBook.Worksheets(1).range("A2").CopyFromRecordset Rs
        
 
vArray = oXLSheet.UsedRange.Value
 
  Set oXLSheet = Nothing
  oXLBook.Save
         oXLBook.Close False
         
         Set oXLBook = Nothing
         Set oXLApp = Nothing
 
cmdMasLoad.Enabled = True
cmdMasLoad.Caption = "Load"
ExceltoSQLSub.Show
Unload Me
End Sub
 
Private Sub Dir1_Change()
File1.FileName = Dir1.Path
End Sub
 
Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
 
End Sub
 
Private Sub File1_Click()
cmdLoad.Enabled = True
End Sub
 
Private Sub File1_DblClick()
cmdLoad_Click
End Sub
 
Private Sub Form_Load()
lstTypes.AddItem "Excel File (.XLS)"
lstTypes.ListIndex = 0
If lstTypes.ListIndex = 0 Then
File1.Pattern = "*.XLS"
Else
End If
Dir1.Path = "C:\"
cmdLoad.Enabled = False
End Sub

Flexgrid Page

Option Explicit
 
Private Sub cmdImport_Click()
Dim strsql As String
Dim ors As Recordset
 
Call Connect
strsql = "Insert into MyTest Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',"
strsql = strsql & "'Excel 8.0;Database=C:\MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')"
Debug.Print CStr(strsql)
Set ors = New Recordset
ors.Open strsql, oConn, adOpenDynamic, adLockOptimistic
Unload Me
frmExceltoSQLMain.Show
End Sub
 
Private Sub Form_Load()
 
 
Dim lngRow As Long, lngCol As Long
  With MSFlexGrid1
    .FixedRows = 1
    .FixedCols = 0
    .Cols = UBound(vArray, 2)   'set the number of rows/cols
    .Rows = UBound(vArray, 1)
    For lngRow = 0 To .Rows - 1 'fill the text
      For lngCol = 0 To .Cols - 1
        .TextMatrix(lngRow, lngCol) = vArray(lngRow + 1, lngCol + 1)
      Next lngCol
    Next lngRow
  End With
 
 
  
  Set vArray = Nothing
  oXLApp.Quit
  Set oXLApp = Nothing
  
End Sub
Private Sub cmdClose_Click()
oConn.Close
Unload Me
frmMain.Show
 
End Sub

and the module

Option Explicit
Public oXLBook As Object
Public oXLSheet As Object
Public my_variable As String
Public oXLApp As Object
Public vArray As Variant
Public lngCol As Long, lngRow As Long
Public oConn As New Connection
 
Public Sub Connect()
 
 
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=(local);DataBase=pubs;password=password;User ID=sa"
 
End Sub
 
Public Sub Connect2()
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};Server=(local);DataBase=mas500_app;password=password;User ID=sa"
 
End Sub

[si_the_geek]

In "main page":
you don't close the objects in cmdLoad_Click.

In "Flexgrid Page":
you dont need a recordset in cmdImport_Click, just use oConn.Execute strsql
in Form_Load, you should not be refering to oXLApp - anything to do with it should be in the place where it is used (presumably cmdLoad_Click in "main page").


The only "Set oXLApp.Visible = True" I can see is fine, but may need a DoEvents before it.

[tibor]

Ok thanks alot for the input. im about to call it a day and will get back to it first thing in the morning and let ya know how it comes about.

[tibor]

Ok i found out why i was getting this error. i was writing it like

Set oXLApp.Visible = True

when it should have been

oXLApp.Visible=True

Thats one problem down, lol.

tibor