Using VBA to access data via the Internet

[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.

 

[Joel]

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.

 

[JeremyJ]

Joel,

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

 

[Joel]

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