Question

Locked

Importing data from external source into excel workbook

By ronhorns ·
It may be easier to state what I want my excel workbook to do and hope that someone out there has the answer.

I have a front end screen in excel and would like to enter in a specific number and press "GO" which will then pull data from another workbook and update certain cells in various sheets, I know this requires VBA but have no idea about coding and just need a little help.

cheers

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

This might be what you are after,

If not then it will give you a few pointers.

Importing data to MS Excel is the process of transferring data from other database sources and converting the data into .xls format. This process eliminates the need to manually enter every item into a new Excel worksheet. Importing data therefore saves the user a lot of time and reduces human error the best way possible.

The source data is called internal data source if it is located in files or folders in your own personal computer.
Importing Data through the Import Data Functionality

On a new Microsoft Excel worksheet, click on the Taskbar menu labeled Data then scroll down to Import External Data. A new menu will pop up on the right. Select Import Data to open the Select Data Source dialog box. Browse over the files in your computer to find your data source or file.

Just remember that the data should be in a format supported by Excel. You will know if your data is importable to Excel if it appears in the default Select Data Source dialog box settings Files of type, All Data Sources.

Once you have selected your data source or file, click Open. The Select Table dialog box will appear. Highlight the particular worksheet you wish to import and click the button OK at the bottom of the dialog box.

The Import Data window will pop up in consequence where you will be asked where you want to put your imported data. Click the Existing Worksheet button if you wish to put the data in your current worksheet or click the button for New Worksheet if you want to add another worksheet to your current MS Excel file to accomodate imported data.

After you have made your choice, click OK at the upper right corner of the window to initiate the data import. If you wish to import other worksheets from the same file, repeat the process. Just select another worksheet when you get to the Select Table window.
Importing Data through Copying and Pasting

If you are going to import data from another .xls file, importing data to a new Excel file can be done through copying and pasting. Simply open a new workbook file or a new worksheet in an existing Excel file. Open, too, the source Excel workbook.

In your source Excel file, select the worksheet you wish to import by clicking on the cell at the upper left corner, the apex cell between Column A and Row 1. This will highlight all the values in the source worksheet.

Right click then select Copy from the pop-up menu or select Edit then Copy from the Excel worksheet Menu Taskbar. Click on your destination Excel workbook tab then click the same apex cell between Column A and Row 1 on your destination worksheet. Right click then select Paste from the right click menu or select Edit from the Menu Taskbar and scroll down to Paste. This will transfer all values, formats, and formula from the source worksheet to the new Excel file.


Please post back if you have more problems or questions.

Collapse -

nearly

by ronhorns In reply to This might be what you ar ...

thanks for that, I can do that no problem, what I want to do is for my current workbook to refer to data in another workbook and pick out a certain row from the number I type in and then it will add the data from that row into my current workbook in various ceels in numerous sheets.

Bit tricky I know and I have to be difficult :)

Collapse -

I think you will have to tell it..

Where to look and then do the update/change through the import/export section, there might be a script that can make it go into auto, but i have not figured that out just yet. I let you know if i do.

Please post back if you have more problems or questions.

Collapse -

Re:Importing data from external source into excel workbook

by anuragvatsa In reply to Importing data from exter ...

Hi dude,
Check it out and please let me know.

string procInfo = "type='text/xsl' href='" + "\\XSLTFile.xslt" + "'";//Processing info for XSLT
string TempPath = Environment.GetFolderPath(Environment.SpecialFolder.InternetCache);
//string XMLPath = TempPath + "\\Transform.xml"; //temp path to store the XML file
string XMLPath = "C:\\Temp.xml";
string XLSPath = @"C:\timesheet.xls"; //temp path to store the XLS file
// string XLSPath = TempPath + "\\Transform.xls";
//Getting the dataset.
DataSet dsReport = new DataSet("ExcelDS");

//Set the table name as specified in the xslt file:
dtReport.TableName = "ExcelTbl";
//Add the copy of table to the above dataset.
dsReport.Tables.Add(dtReport.Copy());

//Write the dataset as XML file with some XSLT processing information
using (XmlTextWriter tw = new XmlTextWriter(XMLPath, null))
{
tw.Formatting = Formatting.Indented;
tw.Indentation = 3;
tw.WriteStartDocument();
DataSet ds = dsReport;
tw.WriteProcessingInstruction("xml-stylesheet", procInfo);
ds.WriteXml(tw);
}
//Create XML Data Document.
XmlDataDocument xmldoc = new XmlDataDocument();
xmldoc.Load(XMLPath);
XslCompiledTransform xsl = new XslCompiledTransform();
//Load the XSLT file.
xsl.Load(XSLTFile);

using (XmlTextWriter tw = new XmlTextWriter(XLSPath, System.Text.Encoding.UTF8))
{
tw.Formatting = Formatting.Indented;
tw.Indentation = 3;
tw.WriteStartDocument();
//Perform a XSLT transformation.
xsl.Transform(xmldoc, null, tw);
}

//Streams the generated XLS file to the user
byte[] Buffer = null;
using (FileStream MyFileStream = new FileStream(XLSPath, FileMode.Open))
{
// Total bytes to read:
long size;
size = MyFileStream.Length;
Buffer = new byte;
MyFileStream.Read(Buffer, 0, int.Parse(MyFileStream.Length.ToString()));
}
//Section for Excel File Download
//Will show the forced download dialog.
HttpContext.Current.Response.ContentType = "application/xls";
string header = "attachment; filename=" + fileName;
HttpContext.Current.Response.AddHeader("content-disposition", header);
HttpContext.Current.Response.BinaryWrite(Buffer);
HttpContext.Current.Response.End();
HttpContext.Current.Response.Flush();


regards,
Vtsa

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums