Export Worksheet to SQL 2008 R2

[RobSully]

I wave a ms Excel Workbook that imports some comma delimited files does some maintenance and combines everything into a single worksheet. Everything works fine and the sheet and the process is very stable. After the sheet is worked throughout the day I import the sheet into a SQL 2008 R2 database where it is then used by other groups. My question is is there a way I can code a Macro that will allow an end user to upload that spreadsheet to SQL SERVER without me having to do it?

 

[BengalMagic]

I wave a ms Excel Workbook that imports some comma delimited files does some maintenance and combines everything into a single worksheet. Everything works fine and the sheet and the process is very stable. After the sheet is worked throughout the day I import the sheet into a SQL 2008 R2 database where it is then used by other groups. My question is is there a way I can code a Macro that will allow an end user to upload that spreadsheet to SQL SERVER without me having to do it?

Yes, I do something similar.

You need to put your database credentials etc into a module and then connect to it - after that, something as simple as

sqlstr = "Use OTR INSERT INTO dbo.invoices (CRNo,[Hours Billed],[Amount Billed],Date,Client,[Inv No],Grade,[Original CR No],Position,CRID) "
sqlstra = sqlstr & "VALUES ('" & (Range("A" & z).Value & "','" & (Range("B" & z).Value & "','" & (Range("C" & z).Value & "','" & (Range("D" & z).Value & "','" & (Range("E" & z).Value & "','")))))
sqlstrb = sqlstra & (Range("F" & z).Value & "','" & (Range("G" & z).Value & "','" & (Range("H" & z).Value & "','" & (Range("I" & z).Value & "','" & (Range("J" & z).Value & "')")))))
gosql.Execute (sqlstrb)
Range("A" & z & ":" & "GG" & z).Select