Export or Import data Excel to Excel (VBA coding)

280 pts.
Tags:
Excel import/export
Microsoft Excel
VBA
Hi experts, could someone please tell me if it's possible to export or import Excel data to Excel (VBA coding)? Ananda Debnath

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you want to achieve this by VBA then there are plenty of option. However, I am trying to describe the cheapest solution here. Please follow the steps below

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/9/2009 by sarfaah

Dim str As String
'This line is added by me Sarfaraz
str = InputBox("Enter the file path")

'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" _
& str & _
";Mode=Share Deny Write;Extend" _
, _
"ed Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine" _
, _
" Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Ne" _
, _
"w Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Co" _
, _
"mpact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Sheet1$")
.Name = "TED Month End Reports"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "D:\TED Month End Reports.xls"
.Refresh BackgroundQuery:=False
End With
End Sub

The above code will be able to take care of your issue. I have added str variable which will ask you to enter the path of MS Excel file.

Regards,
Sarfaraz Ahmed

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Thestappa
    The above works great unless the Str file string is long. If I get to 140 or so characters (deeply buried file) than I get a type mismatch 13 error. Any way around that?
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following