-2

My Question is that we have to create a VBA user form in which there is upload button once we clicked that button only .xls or .xlsx files should be shows and once we select any Excel file the selected file wants to upload in sql server using VBA.

I want the selected excel file data in sql server any ways which is located in Pune and we are in different location suppose like Hyderabad.

Your Help will highly appreciate. I am new in VBA Please help any VBA Champ.

Uttam
  • 1
  • 2
    Your question makes no mention of what help you need. (It just lists what you are trying to do.) It's impossible to tell from the question whether you are asking how to create a form, or had to add a button to it, or how to add code to the button's `Click` event, or how to import an Excel file into SQL, or ..... Please narrow the question down to what your **current** issue is. – YowE3K Jan 13 '18 at 21:01

1 Answers1

0

Well, you can certainly use SSIS to do the work for you.

Question You cannot vote on your own post 0

There are a few ways to do this. Here are few options for you.

http://www.singhvikash.in/2012/11/ssis-how-to-loop-through-multiple-excel.html

https://www.youtube.com/watch?v=1WXKpkwjhX8

http://sqlage.blogspot.in/2013/12/ssis-read-multiple-sheets-from-excel.html

http://beyondrelational.com/modules/24/syndicated/398/Posts/18163/ssis-how-to-loop-through-multiple-excel-sheets-and-load-them-into-a-sql-table.aspx

http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any

http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

Or, link to the Excel files, and import the data.

How to Bulk Insert from XLSX file extension?

Or, save each Excel file as a text file, and loop through all text files in your folder.

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=10000)
BEGIN

PRINT @intFlag


declare @fullpath1 varchar(1000)
select @fullpath1 = '''\\FTP\your_path' + convert(varchar, getdate()- @intFlag , 112) + '_your_file.txt'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[your_table] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 2, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)


SET @intFlag = @intFlag + 1

END
GO

Now, in this example I am looking through a bunch of flies that have almost the same name. The names of the files differ only by the date, which is in the name of the file:

convert(varchar, getdate()- @intFlag , 112)

You must have some kind of similar structure, right. I hope so! Otherwise, it's going to be a lot more difficult to loop through the files.

ASH
  • 20,759
  • 19
  • 87
  • 200