read and write excel files .xlsm in vba (sql server)

[hfs]

i have a database which is connected to sql server,this database reads the excel files paths from a folder and save them into the database...
But i am wondering is there any way from vba that would read all the files (.xlsm) from a folder and save them into sql server (not just the path)!
and then after wards i can write the files from sql server to a folder.
I have done this already in c#.net.... but i dont know how to do that in vba...

need help with this

Thanks!

 

[mdlueck]

You are looking to write the .xlsm binary file contents to a varbinary SQL Server column and are thinking to use Access VBA to read the .xlsm binary files and transfer the content to SQL Server for storage?

Or are you thinking to have Access open the .xlsm files, read the data contained within them, and write each cell's value to SQL Server?

Or or or...? Please clarify what you exactly mean.

 

[hfs]

Hi,
I am looking to write the .xlsm binary file contents to a varbinary SQL Server column and are thinking to use Access VBA to read the .xlsm binary files and transfer the content to SQL Server for storage...

Thanks

 

[mdlueck]

Very well. I believe the only sticky point with what you envision doing is in obtaining a VBA variable which is binary compatible and able to store how ever much data a spreadsheet file has. Variant comes to mind first.

Definitely I would suggest ADO.Command / ADO.Parameters to interface between VBA and the SQL Server. Using that combination completely encapsulates the binary data inside the ADO.Parameter object and you need not pass it raw / concatenated on the SQL line. So some example posts of that style SQL:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

You will only need to come up with the correct ADO.Connection object to connect to SQL Server rather than attaching to the Access FE DB.

Then you will need some File I/O library to read/write the file to the Variant variable.

P.S. As for the SQL Server column data type, probably I would suggest a varbinary(MAX).