Microsoft Excel - VBA codes for updating external data into the sheet

Asked By Lodewyk Jansen on 24-Aug-10 04:07 AM

Good day,

Hope someone can help me. I want to automatically update data that’s downloaded every 24 hours into an excel spread sheet that will use the data to do its calculations.

Yes i am new in VBA but have excellent experience in Excel.


Rolf Jaeger replied to Lodewyk Jansen on 24-Aug-10 10:08 AM
Hi Lodewyk:

you will need to be more specific for anyone here to try to help you out. Please provide the following details: what format are the downloaded data in? what calculations do you need to complete? and probably more.

Best wishes,
Lodewyk Jansen replied to Rolf Jaeger on 24-Aug-10 10:13 AM

The file that I want to upload to excel is in text (.txt). i already have the formulas to do all the calculations I need it to do, so I am not to worried about that, I just need to get the txt data to the sheet in excel.

I already just go and copy past the txt data over into the sheet and then it automatically split the text over to the cells.

So in short i just need a macr that will go and search for the txt file, the name of the file wont change, and copy past the data over to the excel sheet.
Rolf Jaeger replied to Lodewyk Jansen on 24-Aug-10 01:44 PM
Hi Lodewyk:

try using this code to import your data:

Const FILE_PATH As String = "C:\Users\Rolf Jaeger\Documents\Visual Studio 2008\Projects\CSharp\Proteus2000Editor\Proteus2000Editor\bin\Debug\ArpMode.txt"
Const TARGET_SHEET = "ImportedData"
Sub TextImport()
  Dim currentWb As Workbook
  Set currentWb = ActiveWorkbook
  Dim currentSheet As Worksheet
  Set currentSheet = currentWb.Worksheets(TARGET_SHEET)
  Dim wbImportedData As Workbook
  Set wbImportedData = Application.Workbooks.Open(FILE_PATH)
  Dim db As Range
  Set db = ActiveSheet.UsedRange
  db.Copy currentSheet.Range("A1")
  wbImportedData.Close (False)
End Sub

The code needs to be place in a standard VBA module of the workbook you want to import your data into. It assumes that you have a worksheet named "ImportedData" to import the data into. You would then have to write code to deal with the imported data in the context of your solution.

Hope this is what you were looking for,