Using VBA to access data via the Internet

  • Thread starter Thread starter JeremyJ
  • Start date Start date
J

JeremyJ

Our company has multiple locations with a server at the main office where all
of our data is accessed and stored. Is it possible to use VBA from a remote
location to access data in an Excel 2007 Document from that server?

If there is a solution can you please point me in the right direction.
Thank you.
 
You can do it manually while recording a macro. Try the following from
worksheet menu

Data - Import External Data - New Database Query

Select Excel File.
 
Joel,

I don't how but sometimes I forget about that tool. Thank you for the
reply. I'll give it a try.
 
Sub SaveXML()

activesht.Copy
Set NewSht = ActiveSheet
Set NewBK = ActiveWorkbook

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="XML Files (*.xml), *.xml")
If fileSaveName = False Then
MsgBox ("Cannot Save file - Exiting Macro")
Exit Sub
End If


With NewSht.Cells

.Replace "ABC", "DEF"
.Replace ",", ";"
.Replace "^", "$"

End With

NewBK.SaveAs _
Filename:=fileSaveName, _
FileFormat:=xlXMLSpreadsheet

End Sub
 
Back
Top