Free Microsoft Excel 2013 Quick Reference

upload excel file into sql server

hi,

im a beginner and a project is given to me. the program is a bout
uploading excel file into sql server. the user will browse an excel file
after that the sheet and fields will display and the user will select
w/c sheet and fields want to upload same with sql server the user will
select for destination of upload. I already done the other codes for
displaying the excel source fields and destination fields. im having a
hardtime doing the command upload. i use ADO for d conection and
listview control for viewing the fields. hope anyone can give me code.
tnx a lot!

*** Sent via Developersdex http://www.developersdex.com ***


I often use BIDS (2005) SQL Server Integration Services to upload text and
excel files into SQL Server 2005. However, I received an Excel file from an
external source, and when I try to upload it, I get the following error:

[Excel Source [1]] Error: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . The AcquireConnection
method call to the connection manager "Excel Connection Manager" failed with
error code 0xC0202009. There may be error messages posted before this with
more information on why the AcquireConnection method call failed.

Can anyone help?

Hi,

i have a table in an excel file. How can i import it into sql server
express?
Thanks
Ben

Hi,

I am currently creating a web application using VB.NET and SQL Server 2000, which displays a record of glossaries from an SQL Server Database. I have completed the adding of individual glossaries from manual user input using web froms and such.

However, I am trying to implement a function whereby the user is able to upload an excel datasheet to my application or server(not sure how this works). My application will then be able to extract the data from the datasheet and write it into one of the tables in my SQL Server Database.

Any help in this matter will be greatly appreciated as I do not have much of an idea how do I go about to start coding this function.

For reference, my glossary.aspx

	VB:
	
 
 
 
 
Mobile Learning System - Glossary Editor 
 
 
TD IMG { DISPLAY: block } 
.searchBackground { BACKGROUND-IMAGE: url(images/glossary_r2_c8.jpg) } 
 
 
 
 
Function confirm_delete() 
{ 
    If (confirm("Are you sure you want to delete this item?")==True) 
    return true; 
Else 
    return false; 
} 
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
      
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
    
Please ensure both fields are Not empty Term: Definition:
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And my glossary.vb

	VB:
	
Imports System.Data.SqlClient 
Imports System.Data 
Imports System.Text 
Imports System.Web.Security 
Public Class glossary 
Inherits System.Web.UI.Page 
 'Open connection to database
Dim connect_Str As String = "Data Source=localhost;Initial Catalog=mLS;Integrated Security=True;" 
Dim conn As New SqlConnection(connect_Str) 
 
Protected WithEvents Term As System.Web.UI.WebControls.Label 
Protected WithEvents glossTerm As System.Web.UI.WebControls.TextBox 
Protected WithEvents Definition As System.Web.UI.WebControls.Label 
Protected WithEvents glossDef As System.Web.UI.WebControls.TextBox 
Protected WithEvents addGlossBut As System.Web.UI.WebControls.Button 
Protected WithEvents termVal As System.Web.UI.WebControls.RequiredFieldValidator 
Protected WithEvents defVal As System.Web.UI.WebControls.RequiredFieldValidator 
Protected WithEvents addGloss As System.Web.UI.WebControls.Button 
Protected WithEvents lblError As System.Web.UI.WebControls.Label 
Protected WithEvents cancelBut As System.Web.UI.WebControls.Button 
 
 
 
#Region " Web Form Designer Generated Code " 
 
 'This call is required by the Web Form Designer.
 Private Sub InitializeComponent() 
 
End Sub 
Protected WithEvents welcome As System.Web.UI.WebControls.Label 
Protected WithEvents check As System.Web.UI.WebControls.Label 
Protected WithEvents glossData As System.Web.UI.WebControls.DataGrid 
 
 'NOTE: The following placeholder declaration is required by the Web Form Designer.
 'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object 
 
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 
     'CODEGEN: This method call is required by the Web Form Designer
     'Do not modify it using the code editor.
    InitializeComponent() 
End Sub 
 
#End Region 
 
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 
    If Not Page.IsPostBack Then 
        If Session("userID") Is Nothing Then 
            Response.Redirect("index.aspx") 
        End If 
        Dim userID, userRole 
        userID = Session("userID") 
        userRole = Session("userRole") 
        welcome.Visible = True 
        welcome.Text = "You are logged in as: " & userID & "" 
        viewstate("sortField") = "gTerm" 
        viewstate("sortDirection") = "ASC" 
        DisplayData(userID, userRole) 
    End If 
End Sub 
Private Sub glossData_ItemDataBound(ByVal sender As System.Object, ByVal e As DataGridItemEventArgs) Handles
glossData.ItemDataBound 
     
    Dim btn As LinkButton 
    If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then 
        btn = CType(e.Item.Cells(0).FindControl("DelBut"), LinkButton) 
        btn.Attributes.Add("onclick", "return confirm_delete();") 
         
    End If 
     
End Sub 
Public Sub PageChange(ByVal sender As Object, ByVal E As DataGridPageChangedEventArgs) 
    glossData.EditItemIndex = -1 
    lblError.Visible = False 
    glossData.CurrentPageIndex = E.NewPageIndex 
    DisplayData(Session("userID"), Session("userRole")) 
End Sub 
 
Public Sub SortColumn(ByVal Source As Object, ByVal E As DataGridSortCommandEventArgs) 
    If E.SortExpression.ToString() = viewstate("sortField").ToString() Then 
        Select Case viewstate("sortDirection").ToString() 
        Case "ASC" 
            viewstate("sortDirection") = "DESC" 
        Case "DESC" 
            viewstate("sortDirection") = "ASC" 
        End Select 
    Else 
        viewstate("sortField") = E.SortExpression 
        viewstate("sortDirection") = "ASC" 
    End If 
    DisplayData(Session("userID"), Session("userRole")) 
End Sub 
 
Private Sub DataGrid1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) 
     
End Sub 
 
Public Function DisplayData(ByVal userID As String, ByVal userRole As String) 
     
     
    Try 
    conn.Open() 
    Dim strSQL As String 
     'Checking for User Role
    If userRole = 1 Then 
        strSQL = "SELECT gID, gTerm, gDef, Contributor FROM Glossary ORDER BY " & viewstate("sortField").ToString() & " " &
viewstate("sortDirection").ToString() 
    ElseIf userRole = 2 Then 
        strSQL = "SELECT gID, gTerm, gDef, Contributor FROM Glossary WHERE Contributor = '" & userID & "'" 
    End If 
    Dim sqlCmd As New SqlDataAdapter(strSQL, conn) 
    Dim rs As New DataSet 
    sqlCmd.Fill(rs, "Glossary") 
     'Dim dataTbl As DataTable = rs.Tables("Glossary")
     'Dim dvView As New DataView(dataTbl)
     'dvView.Sort = viewstate("sortField")
    check.Visible = True 
    check.Text = "Loaded" 
    glossData.DataSource = rs 
    glossData.DataBind() 
    conn.Close() 
    Catch exc As Exception 
    check.Visible = True 
    check.Text = exc.Message 
    End Try 
End Function 
Public Sub EditRecord(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs) 
     
    Term.Visible = False 
    Definition.Visible = False 
    glossTerm.Visible = False 
    glossTerm.Text = "" 
    glossDef.Visible = False 
    glossDef.Text = "" 
    addGlossBut.Visible = False 
    cancelBut.Visible = False 
     
    glossData.EditItemIndex = e.Item.ItemIndex 
    DisplayData(Session("userID"), Session("userRole")) 
     
End Sub 
Public Sub CancelEdit(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs) 
     
    glossData.EditItemIndex = -1 
    DisplayData(Session("userID"), Session("userRole")) 
    lblError.Visible = False 
     
End Sub 
Public Sub UpdateRecord(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs) 
     
    Dim PrimaryKey As Int32 = Convert.ToInt32(glossData.DataKeys(e.Item.ItemIndex)) 
    Dim strTermTB As TextBox = CType(e.Item.Cells(0).Controls(0), TextBox) 
    Dim strDefTB As TextBox = CType(e.Item.Cells(1).Controls(0), TextBox) 
    Dim strTerm As String = Convert.ToString(strTermTB.Text) 
    Dim strDef As String = Convert.ToString(strDefTB.Text) 
    If strTerm = "" Or strDef = "" Then 
        lblError.Visible = True 
        termVal.Visible = False 
        defVal.Visible = False 
    Else 
        glossData.EditItemIndex = -1 
        check.Visible = True 
        check.Text = "UpdateRec:" & strTerm 
        UpdateData(PrimaryKey, strTerm, strDef) 
        lblError.Visible = False 
    End If 
End Sub 
Private Sub UpdateData(ByVal PrimaryKey As Long, ByVal strTerm As String, ByVal strDef As String) 
     'Create and load a DataSet with records from Glossary Table
    conn.Open() 
    Dim strSelect As String = "SELECT gID, gTerm, gDef, Contributor FROM Glossary" 
    Dim adapter As New SqlDataAdapter(strSelect, conn) 
    Dim ds As New DataSet 
    adapter.Fill(ds, "Glossary") 
    conn.Close() 
     'Modify the in-memory records in the Dataset
    Dim tbl As DataTable = ds.Tables("Glossary") 
    tbl.PrimaryKey = New DataColumn() {tbl.Columns("gID")} 
    Dim row As DataRow = tbl.Rows.Find(PrimaryKey) 
    row.Item("gTerm") = strTerm 
    row.Item("gDef") = strDef 
     'Re-connect the DataSet and update the database
    Dim sqlCmd As New SqlCommandBuilder(adapter) 
    conn.Open() 
    adapter.Update(ds, "Glossary") 
    conn.Close() 
     ' Reset & Bind Datagrid
    glossData.DataSource = ds.Tables("Glossary") 
    glossData.DataBind() 
    termVal.Visible = False 
    defVal.Visible = False 
     
End Sub 
 
Public Sub DeleteRecord(ByVal sender As System.Object, ByVal e As DataGridCommandEventArgs) 
     
    Dim PrimaryKey As Int32 = Convert.ToInt32(glossData.DataKeys(e.Item.ItemIndex)) 
    glossData.EditItemIndex = -1 
    DeleteData(PrimaryKey) 
     
End Sub 
 
Private Sub DeleteData(ByVal PrimaryKey As Long) 
     
    conn.Open() 
    Dim strSelect As String = "SELECT gID, gTerm, gDef, Contributor FROM Glossary" 
    Dim adapter As New SqlDataAdapter(strSelect, conn) 
    Dim ds As New DataSet 
    adapter.Fill(ds, "Glossary") 
    conn.Close() 
    Dim tbl As DataTable = ds.Tables("Glossary") 
    tbl.PrimaryKey = New DataColumn() {tbl.Columns("gID")} 
    Dim row As DataRow = tbl.Rows.Find(PrimaryKey) 
    row.Delete() 
    Dim sqlCmd As New SqlCommandBuilder(adapter) 
    conn.Open() 
    adapter.Update(ds, "Glossary") 
    conn.Close() 
     
    glossData.DataSource = ds.Tables("Glossary") 
    glossData.DataBind() 
    termVal.Visible = False 
    defVal.Visible = False 
End Sub 
 
Private Sub glossData_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
glossData.SelectedIndexChanged 
     
End Sub 
 
Private Sub addGloss_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addGloss.Click 
    glossData.EditItemIndex = -1 
    DisplayData(Session("userID"), Session("userRole")) 
    lblError.Visible = False 
    Term.Visible = True 
    Definition.Visible = True 
    glossTerm.Visible = True 
    glossDef.Visible = True 
    addGlossBut.Visible = True 
    cancelBut.Visible = True 
    termVal.Visible = True 
    defVal.Visible = True 
End Sub 
 
Private Sub cancelBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cancelBut.Click 
    Term.Visible = False 
    Definition.Visible = False 
    glossTerm.Visible = False 
    glossTerm.Text = "" 
    glossDef.Visible = False 
    glossDef.Text = "" 
    addGlossBut.Visible = False 
    cancelBut.Visible = False 
End Sub 
 
Private Sub addGlossBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addGlossBut.Click 
    If Not Page.IsValid Then 
        termVal.Visible = True 
        defVal.Visible = True 
    Else 
        conn.Open() 
        Dim strSelect As String = "SELECT gID, gTerm, gDef, Contributor FROM Glossary" 
        Dim adapter As New SqlDataAdapter(strSelect, conn) 
        Dim ds As New DataSet 
        adapter.Fill(ds) 
        conn.Close() 
         'Create a new row
        Dim r As DataRow = ds.Tables(0).NewRow() 
         'Add gTerm, gDef and COntributor
        r("gTerm") = glossTerm.Text 
        r("gDef") = glossDef.Text 
        r("Contributor") = Session("userID") 
         'Add the new row into the dataset
        ds.Tables(0).Rows.Add(r) 
         'Update the database
        Dim sqlCmd As New SqlCommandBuilder(adapter) 
        conn.Open() 
        adapter.Update(ds) 
        conn.Close() 
        Term.Visible = False 
        Definition.Visible = False 
        glossTerm.Visible = False 
        glossTerm.Text = "" 
        glossDef.Visible = False 
        glossDef.Text = "" 
        addGlossBut.Visible = False 
        cancelBut.Visible = False 
        DisplayData(Session("userID"), Session("userRole")) 
    End If 
End Sub 
End Class 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm looking for a method of doing a large update into a SQL server from the data in an excel file. The method needs to actually go directly from excel into the SQL server not just using an "import" function through enterprise manager.

By doing this I can do a large update of information directly from users rather requiring them to type it into a web interface.

I have seen a few products that allow you to do this however the price is a little prohibitive. Does anyone know of a macro or something else out there that will allow me to do this?

Hi, for bulk insert, I guess, you have to export your excel data as a TAB
separated text file and then try using SQL Server's bcp utility to choose the
table into which you want to insert the data and the data file. This should
take care of the issue. For further details you can look into SQL Server's
help documentation on bulk insert or bcp.

"Doctorjones_md" wrote:

> I'm trying to export data from an Excel worksheet into SQL Server. I've
> heard that Bulk Insert or bcp might be the way to go, but I don't know
> anything about how to go about setting that up. If this would be the most
> efficient method for transfering data from Excel to SQL, could some please
> point me in the right direction for modifying my existing code (Shown
> Below).
>
> I recently attempted to follow someones suggestion to used Stored
> Procedures -- my 1st attempt at writing these .... The latest code that
> I've tried using gave me the following error:
> "Runtime error '3001'
> Arguments are of the wrong type, are out of acceptable range, or are in
> conflict with one another.
>
> Any ideas into what's causing the error? Could it be a conflict in data
> types that's causing this?
>
> Here's the code I was running:
> ====================================
> 1. The following Stored Procedure on SQL Server
> ==================================
> create procedure dbo.usp_Insert_Upload_Specific
> @Loc vchar(5),
> @PType vchar(5),
> @Quant integer,
> @PName vchar(25),
> @Style vchar(5),
> @Features vchar(25)
>
> AS
>
> INSERT INTO Upload_Specific
> (
> Location,
> [Product Type],
> Quantity,
> [Product Name],
> Style,
> Features
> )
> VALUES
> (
> @Loc,
> @PType,
> @Quant,
> @PName,
> @Style,
> @Features,
> )
>
> GO
>
> 1. The following VBA code in Excel Module
> ===============================
>
> Sub InsertData()
> Dim oConn As Object
> Dim sSQL As String
> Application.ScreenUpdating = False
> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=sqloledb;" & _
> "Data Source=xx.x.xx.xx;" & _
> "Initial Catalog=xxx_xxx;" & _
> "User Id=xxxx;" & _
> "Password=xxxx"
>
> ' NEW CODE HERE
> Dim intParams As Integer
> Dim objCmd As New ADODB.Command
>
> ' Connect the Command object to the data source.
> objCmd.ActiveConnection = objConn
>
> ' Set CommandText equal to the stored procedure name.
> objCmd.CommandText = "dbo.usp_Insert_Upload_Specific"
> objCmd.CommandType = adCmdStoredProc
>
> ' Automatically fill in parameter info from stored procedure.
> objCmd.Parameters.Refresh
>
> ' Get the count of required parameters SHOULD BE 20
> intParams = objCmd.Parameters.Count - 1 'first one is RETURN value
>
> DIM rng as Range
> DIM ccell as Range
> set rng = range("A2:T20") ' change this range to include all your data
> For Each ccell in rng
> ' call the stored procedure
>
> for x = 1 to intParams
> objCmd(x) = ccell.offset(0,x-1)
> next x
>
> ' now that all the parameters have been assigned values
> ' execute the query
> objCmd.execute
>
> next ccell
>
> 'close the connection
> oConn.Close
> Set oConn = Nothing
> End Sub
> ==================
> Initially, I tried this code (which effectively does the following):
>
> 1. Deletes all rows having a value of "0" in column C
> 2. Uploads the data in Row 2 to my SQL Server
>
> What I need for the code to do is to upload all rows on the worksheet --
> how would I modify the code to upload all rows, or iterate on each row
> having data?
>
> Here's my code:
> ========================
> Private Sub DeleteBlankRows()
>
> Dim lastrow As Long
> Dim r As Long
> lastrow = Range("C" & Rows.Count).End(xlUp).Row
> For r = lastrow To 2 Step -1
> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
> ActiveSheet.Rows(r).Delete
> End If
> Next
>
> End Sub
>
> Sub InsertData()
> Dim oConn As Object
> Dim sSQL As String
> Application.ScreenUpdating = False
> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=sqloledb;" & _
> "Data Source=xx.x.xx.xx;" & _
> "Initial Catalog=xxx_xxx;" & _
> "User Id=xxxx;" & _
> "Password=xxxx"
> sSQL = "INSERT INTO Upload_Specific " & _
> "([Location], [Product Type], [Quantity], [Product Name], [Style],
> [Features]) " & _
> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '"
> &
> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
> Range("E2").Value
> & "', '" & _
> Range("F2").Value & "')"
> oConn.Execute sSQL
> oConn.Close
> Set oConn = Nothing
> End Sub
>
> Thanks in advance.
>
>
>
>
>
>

Problem: Error when trying to query the excel file
"The Microsoft Jet database engine cannot open the file.
It is already opened exclusively by another user, or you need permission to
view its data."

We are developing a .NET component which automates the Excel to generate
some data and exports that data into SQL Server. Here are the steps for the
process:
1. Opens the pre formatted Excel file with required formulas in it.
2. Inserts the data into that spreadsheet.
3. Add/Remove some formulas depending on the conditions.
4. Save the file with different name and close the file.
5. Query the saved file to insert the data in to SQL using OleDb.

This process generates at least 12 files in a minute.

The problem is when we try to query the file. During this process we are
getting the below error:
"The Microsoft Jet database engine cannot open the file.
It is already opened exclusively by another user, or you need permission to
view its data."

I guess this problem may be occurring because the method that it created
this file is still holding this file. I tried to cleanup several ways by
using ReleaseComObject and GC etc. But it works well for first 3 or 4 files
again starts throwing the same error.

Hi,
i want to import excel sheet data into sql server 2005 table.i know transferspreadsheet in vba is used to import excel data into access table.similarly i want in vba code of importing excel sheet data into sql server 2005 table.reply me immediately as early as possible.

Hi,

I have found a way to link to Excel 2003 in Sql 2k5. But I cannot link to a
Excel 2007 one. In the link to Excel 2k3, I used the "Produtc String" equal
"Excel 8.0;'. But I do not know what should I use for Excel 2007. I see the
file version of "excel.exe" is 12..., I tried this one, but it is not working.

I know I can always easliy import the excel data into sql using SSIS. I just
want to know if it is possible to link to the Excel 2007 file directly.
Anyway, SSIS is not free

Hello,
This is my first post so if anything is wrong with it I apoligize in advance. I have looked everywhere and come up empty handed trying to make a macro that exports an excel table to SQL server through an odbc connection. As of right now, I currently am able to access the database and import a particular table to my spreadsheet. The code that makes this connection is here:

With Sheet2.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=" + DSN + ";Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=" +
DATABASE + ";" _
        , Destination:=Range("Sheet2!$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT * FROM " + DATABASE + ".dbo." + TABLE + " " + TABLE _
        )
Now what I want to do is set up something similar to go the other direction, using INSERT statements to populate a database table with the table that had been imported into Excel. Any help in setting up the initial connection or a reference would be great. It seems there is a lot of information out there for other connection types and scenarios, but nothing I could find on this. Thanks a lot.

I need to import data from an excel file into SQL Server database. User uploads files using an ASP web application. User can upload any excel file (97 - 2007). Currently a VB COM component is implemented which supports upto Excel 2003. How do I extend or revamp the solution (if required) to support Excel 2007 also. Thanks

I have found a ton of information on how to import an Access table into Excel, but I have come up short when I have been looking for a way to import an Excel file into Access via VBA.

Currently, I have 3 tables that I update monthly manually by means of the Import Wizard, but I am looking to automate the process as much as possible.

At the least, I would like to hit a button in a form that imports a particular Excel file (the name doesn't change from month to month) and at least brings me into the import wizard.

I am just starting out in VBA, and I know this is going to require a bit of code, so I'm reaching out for HELP!

Big thanks in advance for helping me out...

Paul

Even though there are similar problems in a few threads, I could not find a solution to my problem, so I ask for help.

The problem is to sort of consolidate data inside several hundreds of Excel files within a directory tree, by copying values into a new Excel file, into a new row from each external workbook. The file name is exactly the same for each of the referenced Excel files, sheet name likewise, just as well the data structure of the sheets. We literally talk about hundreds of files containing more than a hundred data cells each.
When I started with this problem, I managed to write a master Macro-enabled Excel file, that has links to an unknown 'File1' and this solution was kinda OK with up to a 25-30 external files: after populating a single raw from a single external file I saved the new file and repeated the process with every external file, then copied the rows after each other into a new empty file. With more than 500 files I need a different solution, one that only needs to be started and a script would fetch the content of the referenced data cells for every existing files, then make a new row and move to the next file and fetch again, etc.

Since the number of referenced cells is also quite high, it may be a very frightening-looking script.
The first cell to be fetched is a string (a name), the second and third are dates, and all the rest is numeric.

I attach the master macro-enabled file to show a single row of the wished result (master.xlsm), and an example of the source file (example.xls).

Any type of solution would be appreciated, although VBScript is not my strength.

example.xlsmaster.xlsm

Hi there!

So I have some VBA code that saves to network drive Excel files based on their store number, after it all finishes to process using the VBA script, it creates a log sheet which shows me all the Excel files that were saved and processed. The log file is just a plain sheet that records the locations of the save in the network drive and the excel file name.

I send a notification to employees, they use those files and fill in some data into the sheets and then at the end of the month, we need to grab those files back from the network drive for analysis. The files are in the same exact network path and the file names will not change, what would be the best way to grab those files back from the network drive and import them into a workbook using VBA ?

I was thinking of using something that would be able to work by selection, or I select the rows and then have Excel import the selected files into a single workbook, to give an example:

Let say that I have 5 rows in the Log sheet in column A and each row represents a successful save:

COLUMN A

Row1: networklocationlistsregion1Excel_doc_1.xls
Row2: networklocationlistsregion1Excel_doc_569.xls
Row3: networklocationlistsregion2Excel_doc_587.xls
Row4: networklocationlistsregion2Excel_doc_2.xls
Row5: networklocationlistsregion2Excel_doc_6.xls

I would like to be able to play a VBA script, it would allow me to highlight by selection the range that I want to process, so in this example I would highlight A1:A5 and then I would continue the VBA procedure and Excel would import those 5 Excel files into a single workbook, so 5 sheets in this case, each representing the document imported ?

I was looking into Application.GetOpenFilename, but I am not sure what the best way is yet, I figured I would start here and get every ones opinion.

Thanks a lot in advance for your help and assistance.

I have an 8 meg excel file that needs to be shared with a whole team who
connects to our web site with a very slow connection. Is there a way to
"run" an Excel file on a server, so that the user is actually only loading
the "tab" that they are using vs. loading the entire file everytime they want
to look one thing up.

This Excel file is updated regularily. Sadly, it won't work to just
distribute it on CDs.

Thanks in advance!

Jon

I am trying to import an excel file into Outlook. When I go through the
import function it is putting each column into one contact but not all of
that contacts information. So, I will have a list of contacts with just the
company name. Is there something else I have to do to the Excel file before
importing?

I am trying to find a way of importing data from several different excel
files into different sheets of the same excel file (in a specific format).
Then, I am planning to consolidate these sheets into one. The data that is
going to be imported will be refreshed often. Please note that I would not
like to use links. Thank you!

Is there a way to insert another Excel file into an existing Excel file?
A student recently asked and it got me to thinking.
The only thing that seems to work is the old copy and paste.
I know in Word you can insert a file and when I tried this in Excel it ended
up inserting the file as an embeded object.
they have several lists in separate files and would like the quickest way to
combine them into one big list.
Any suggestions and/or resources to check out would be appreciated.
Thanks!
Jugglertwo

I am using Excel 2007 with SQL Server Analysis Services 2005. I want to
create calculated measure based on other measures, but I cannot do this for
Pivot table. If I go to menu "Options"->"Formulas" I can see that it is
disabled.

Can anyone let me know how to enable the "Formulas" in Options for Pivot
table.

Thanks

Office 2002

When I am in word, I can "insert" a file into the file I am presently
working on.
I simple click on INSERT & select file.

I do not see this option in Excel but it would really be helpful.

Any suggestions on how I can insert an excel file into the excel file
currently being worked on?

MR

Hi,
I need help on how to make the thousand separator appears after I merged the
excel file into word document. I was able to merge my excel file - the data
source into word document, but the thousand separator didn't appear with the
numbers after i merged it into the word document. I also baffled why the
parenthesis would change to a minus sign after i merged it. Can any one
help? I am really desparate. Thanks so much!

Lynn

I imported an xl spreadsheet data into sql server database. I notice that
some columns data is NULL even though the data exists in spreadsheet.
However, the entire columns isn't null, some columns it converts correctly
whereas for others it puts . I tried converting the column into both
text and numeric fields, but I got same results. This is happening for a few
columns, some columns have been converted correctly.

Any ideas as to why it may be happening? How can I fix this?

I need to fix this asap as I have project deadline to meet.

If this is not the right group for this question, please let me know.

Thank you very much,
-Me

Is there a way to query the Jet OLEDB Provider for Excel
to find out the COlumn Names and DataTypes of an Excel
worksheet? I want to import an Excel file into SQL Server
and need to create the table on the fly for that file and
set the column datatypes for that table. What would be
the best way to accomplish this? Thanks!

Hi,

I have tried a few macro codes for merging several similar excel files
into one but all of them (the ones that actually worked) did not read
the 'correct' worksheet withing the XLS.

Can anyone give me a hint?

Thank you so much!!!

Alex

where do i download Excel 2007 for SQL Server Analysis Services?