Cinema
06-02-2016, 01:41 PM
Hi,
I have a code written with "record macro" that converts a DBF data into excel. Since the DBF data is too big, I search a specific area and copy the area from DBF into the Excel Sheet.
Now I have a problem with my path (C:\costs\Results) given in the code. I want to change the path in my excel sheet; the path should be on Sheet1 in Cell(1,1). Instead of the path in the code there should be a something like ActiveSheet.Cells(1.1).Value but it doesn't work.
Have someone an idea how to change this?
Here is the code:
Sub Makro1()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=VFPOLEDB.1;Data Source=C:\costs\Results;Mode=Share Deny None;Extende" _
, _
"d Properties="""";User ID="""";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN=" _
, """"""), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("Select * From `company~main~cost` Where ALLTRIM(group) like ""%|Costing""")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Standard__company_main_cost"
.Refresh
End With
End Sub
Edit: Moved thread from "Announcements" to "Excel Help." Moderator
I have a code written with "record macro" that converts a DBF data into excel. Since the DBF data is too big, I search a specific area and copy the area from DBF into the Excel Sheet.
Now I have a problem with my path (C:\costs\Results) given in the code. I want to change the path in my excel sheet; the path should be on Sheet1 in Cell(1,1). Instead of the path in the code there should be a something like ActiveSheet.Cells(1.1).Value but it doesn't work.
Have someone an idea how to change this?
Here is the code:
Sub Makro1()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=VFPOLEDB.1;Data Source=C:\costs\Results;Mode=Share Deny None;Extende" _
, _
"d Properties="""";User ID="""";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN=" _
, """"""), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("Select * From `company~main~cost` Where ALLTRIM(group) like ""%|Costing""")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Standard__company_main_cost"
.Refresh
End With
End Sub
Edit: Moved thread from "Announcements" to "Excel Help." Moderator