Hi! I want to format my excel data that's been imported from MS Access, such
that, ther's a box around all the data cells, also please help me, about how
to have my aggregate fields at the end of the data. Can this be achieved
using MS Excel 2007, automation using macros, or VBA. I'd much appreciate
your guiding remarks. Thanks in advance.
Regards,
Imran.

both can be done quite easily...but we'd need to see the code that loads the
sheet with the MS Access data to get a handle on the method used in order to
give you an appropriate response.

for example you may have
Range("A1").CopyFromrecordset rst
or various other methods of loading the data to the sheet.



"Imran Ghani" <(E-Mail Removed)> wrote in message
news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
> Hi! I want to format my excel data that's been imported from MS Access,
> such
> that, ther's a box around all the data cells, also please help me, about
> how
> to have my aggregate fields at the end of the data. Can this be achieved
> using MS Excel 2007, automation using macros, or VBA. I'd much appreciate
> your guiding remarks. Thanks in advance.
> Regards,
> Imran.

Hi Patrick Molloy

I am exporting my data with a query as:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryinvrgstr",
"d:\invoice register.xls", True

Its working fine and exporting the data ok, but I'd like also to get totals
in the end of the data and also to format my whole data with lines all
around. I'd much appreciate your guidance.

Regards,
Imran.

"Patrick Molloy" wrote:

> both can be done quite easily...but we'd need to see the code that loads the
> sheet with the MS Access data to get a handle on the method used in order to
> give you an appropriate response.
>
> for example you may have
> Range("A1").CopyFromrecordset rst
> or various other methods of loading the data to the sheet.
>
>
>
> "Imran Ghani" <(E-Mail Removed)> wrote in message
> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
> > Hi! I want to format my excel data that's been imported from MS Access,
> > such
> > that, ther's a box around all the data cells, also please help me, about
> > how
> > to have my aggregate fields at the end of the data. Can this be achieved
> > using MS Excel 2007, automation using macros, or VBA. I'd much appreciate
> > your guiding remarks. Thanks in advance.
> > Regards,
> > Imran.
>
>

ah. from your question I thought Excel was driving the import, but in fact
MS Access is doing this..so AFAIK you can't format this way, you'd have to
write more VBA in Access to open the spreadsheet then format it. I don't
think Access gives you much control.
What would be "better" is to instantiate excel, load the table from the
query and then you'd have more control over the excel range.
Do you know how to do this?


"Imran Ghani" <(E-Mail Removed)> wrote in message
news:7C27D37A-E9FC-4F84-89DA-(E-Mail Removed)...
> Hi Patrick Molloy
>
> I am exporting my data with a query as:
>
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> "qryinvrgstr",
> "d:\invoice register.xls", True
>
> Its working fine and exporting the data ok, but I'd like also to get
> totals
> in the end of the data and also to format my whole data with lines all
> around. I'd much appreciate your guidance.
>
> Regards,
> Imran.
>
> "Patrick Molloy" wrote:
>
>> both can be done quite easily...but we'd need to see the code that loads
>> the
>> sheet with the MS Access data to get a handle on the method used in order
>> to
>> give you an appropriate response.
>>
>> for example you may have
>> Range("A1").CopyFromrecordset rst
>> or various other methods of loading the data to the sheet.
>>
>>
>>
>> "Imran Ghani" <(E-Mail Removed)> wrote in message
>> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
>> > Hi! I want to format my excel data that's been imported from MS Access,
>> > such
>> > that, ther's a box around all the data cells, also please help me,
>> > about
>> > how
>> > to have my aggregate fields at the end of the data. Can this be
>> > achieved
>> > using MS Excel 2007, automation using macros, or VBA. I'd much
>> > appreciate
>> > your guiding remarks. Thanks in advance.
>> > Regards,
>> > Imran.
>>
>>

Hi Patrick Molloy
Thanks for your reply. Kindly do guide me about the whole process, mentioned
by you, as I, being a novice, am certainly not much capable to handle the job
independently. I'd much appreciate your helping hand.
Regards,
Imran.

"Patrick Molloy" wrote:

> ah. from your question I thought Excel was driving the import, but in fact
> MS Access is doing this..so AFAIK you can't format this way, you'd have to
> write more VBA in Access to open the spreadsheet then format it. I don't
> think Access gives you much control.
> What would be "better" is to instantiate excel, load the table from the
> query and then you'd have more control over the excel range.
> Do you know how to do this?
>
>
> "Imran Ghani" <(E-Mail Removed)> wrote in message
> news:7C27D37A-E9FC-4F84-89DA-(E-Mail Removed)...
> > Hi Patrick Molloy
> >
> > I am exporting my data with a query as:
> >
> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> > "qryinvrgstr",
> > "d:\invoice register.xls", True
> >
> > Its working fine and exporting the data ok, but I'd like also to get
> > totals
> > in the end of the data and also to format my whole data with lines all
> > around. I'd much appreciate your guidance.
> >
> > Regards,
> > Imran.
> >
> > "Patrick Molloy" wrote:
> >
> >> both can be done quite easily...but we'd need to see the code that loads
> >> the
> >> sheet with the MS Access data to get a handle on the method used in order
> >> to
> >> give you an appropriate response.
> >>
> >> for example you may have
> >> Range("A1").CopyFromrecordset rst
> >> or various other methods of loading the data to the sheet.
> >>
> >>
> >>
> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
> >> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
> >> > Hi! I want to format my excel data that's been imported from MS Access,
> >> > such
> >> > that, ther's a box around all the data cells, also please help me,
> >> > about
> >> > how
> >> > to have my aggregate fields at the end of the data. Can this be
> >> > achieved
> >> > using MS Excel 2007, automation using macros, or VBA. I'd much
> >> > appreciate
> >> > your guiding remarks. Thanks in advance.
> >> > Regards,
> >> > Imran.
> >>
> >>

I'm out of the office for 2 days - so i'm afraid I cannot help until
Saturday. However, this code is excel vba and should be clear enough to get
you going.
Open excel, go to the development environment (ALT+F11), add a module
(Insert/Module) then paste the code:
change the SQL statement and the name of the MS Access database
appropriately

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim i As Long

MyFile = "Risk.mdb"
SQL = "SELECT * FROM BondTable"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

rst.Open SQL, con, adOpenStatic

Cells.Clear

For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next

Range("A2").CopyFromRecordset rst

rst.Close
con.Close

Set rst = Nothing
Set con = Nothing

End Sub



"Imran Ghani" <(E-Mail Removed)> wrote in message
news:C2F034F3-D9F0-41D7-837C-(E-Mail Removed)...
> Hi Patrick Molloy
> Thanks for your reply. Kindly do guide me about the whole process,
> mentioned
> by you, as I, being a novice, am certainly not much capable to handle the
> job
> independently. I'd much appreciate your helping hand.
> Regards,
> Imran.
>
> "Patrick Molloy" wrote:
>
>> ah. from your question I thought Excel was driving the import, but in
>> fact
>> MS Access is doing this..so AFAIK you can't format this way, you'd have
>> to
>> write more VBA in Access to open the spreadsheet then format it. I don't
>> think Access gives you much control.
>> What would be "better" is to instantiate excel, load the table from the
>> query and then you'd have more control over the excel range.
>> Do you know how to do this?
>>
>>
>> "Imran Ghani" <(E-Mail Removed)> wrote in message
>> news:7C27D37A-E9FC-4F84-89DA-(E-Mail Removed)...
>> > Hi Patrick Molloy
>> >
>> > I am exporting my data with a query as:
>> >
>> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
>> > "qryinvrgstr",
>> > "d:\invoice register.xls", True
>> >
>> > Its working fine and exporting the data ok, but I'd like also to get
>> > totals
>> > in the end of the data and also to format my whole data with lines all
>> > around. I'd much appreciate your guidance.
>> >
>> > Regards,
>> > Imran.
>> >
>> > "Patrick Molloy" wrote:
>> >
>> >> both can be done quite easily...but we'd need to see the code that
>> >> loads
>> >> the
>> >> sheet with the MS Access data to get a handle on the method used in
>> >> order
>> >> to
>> >> give you an appropriate response.
>> >>
>> >> for example you may have
>> >> Range("A1").CopyFromrecordset rst
>> >> or various other methods of loading the data to the sheet.
>> >>
>> >>
>> >>
>> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
>> >> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
>> >> > Hi! I want to format my excel data that's been imported from MS
>> >> > Access,
>> >> > such
>> >> > that, ther's a box around all the data cells, also please help me,
>> >> > about
>> >> > how
>> >> > to have my aggregate fields at the end of the data. Can this be
>> >> > achieved
>> >> > using MS Excel 2007, automation using macros, or VBA. I'd much
>> >> > appreciate
>> >> > your guiding remarks. Thanks in advance.
>> >> > Regards,
>> >> > Imran.
>> >>
>> >>

Hi Patrick Molloy!

I hope you have a good time during your holidays.
I have written my code in excel according to your guidance, but its giving
an error message of:

user defined type not defined and highlighting the following text:

Dim con As New ADODB.Connection

Kindly guide me in this respect.
Regards,
Imran.

"Patrick Molloy" wrote:

> I'm out of the office for 2 days - so i'm afraid I cannot help until
> Saturday. However, this code is excel vba and should be clear enough to get
> you going.
> Open excel, go to the development environment (ALT+F11), add a module
> (Insert/Module) then paste the code:
> change the SQL statement and the name of the MS Access database
> appropriately
>
> Sub LoadDataFromAccess()
> Dim MyFile As String
> Dim con As New ADODB.Connection
> Dim rst As New ADODB.Recordset
> Dim SQL As String
> Dim i As Long
>
> MyFile = "Risk.mdb"
> SQL = "SELECT * FROM BondTable"
>
> con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
>
> rst.Open SQL, con, adOpenStatic
>
> Cells.Clear
>
> For i = 0 To rst.Fields.Count - 1
> Cells(1, i + 1).Value = rst.Fields(i).Name
> Next
>
> Range("A2").CopyFromRecordset rst
>
> rst.Close
> con.Close
>
> Set rst = Nothing
> Set con = Nothing
>
> End Sub
>
>
>
> "Imran Ghani" <(E-Mail Removed)> wrote in message
> news:C2F034F3-D9F0-41D7-837C-(E-Mail Removed)...
> > Hi Patrick Molloy
> > Thanks for your reply. Kindly do guide me about the whole process,
> > mentioned
> > by you, as I, being a novice, am certainly not much capable to handle the
> > job
> > independently. I'd much appreciate your helping hand.
> > Regards,
> > Imran.
> >
> > "Patrick Molloy" wrote:
> >
> >> ah. from your question I thought Excel was driving the import, but in
> >> fact
> >> MS Access is doing this..so AFAIK you can't format this way, you'd have
> >> to
> >> write more VBA in Access to open the spreadsheet then format it. I don't
> >> think Access gives you much control.
> >> What would be "better" is to instantiate excel, load the table from the
> >> query and then you'd have more control over the excel range.
> >> Do you know how to do this?
> >>
> >>
> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
> >> news:7C27D37A-E9FC-4F84-89DA-(E-Mail Removed)...
> >> > Hi Patrick Molloy
> >> >
> >> > I am exporting my data with a query as:
> >> >
> >> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> >> > "qryinvrgstr",
> >> > "d:\invoice register.xls", True
> >> >
> >> > Its working fine and exporting the data ok, but I'd like also to get
> >> > totals
> >> > in the end of the data and also to format my whole data with lines all
> >> > around. I'd much appreciate your guidance.
> >> >
> >> > Regards,
> >> > Imran.
> >> >
> >> > "Patrick Molloy" wrote:
> >> >
> >> >> both can be done quite easily...but we'd need to see the code that
> >> >> loads
> >> >> the
> >> >> sheet with the MS Access data to get a handle on the method used in
> >> >> order
> >> >> to
> >> >> give you an appropriate response.
> >> >>
> >> >> for example you may have
> >> >> Range("A1").CopyFromrecordset rst
> >> >> or various other methods of loading the data to the sheet.
> >> >>
> >> >>
> >> >>
> >> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
> >> >> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
> >> >> > Hi! I want to format my excel data that's been imported from MS
> >> >> > Access,
> >> >> > such
> >> >> > that, ther's a box around all the data cells, also please help me,
> >> >> > about
> >> >> > how
> >> >> > to have my aggregate fields at the end of the data. Can this be
> >> >> > achieved
> >> >> > using MS Excel 2007, automation using macros, or VBA. I'd much
> >> >> > appreciate
> >> >> > your guiding remarks. Thanks in advance.
> >> >> > Regards,
> >> >> > Imran.
> >> >>
> >> >>
>

" I hope you have a good time during your holidays. " ?? what holidays?

In the development application go to Tools /References and check Microsoft
Active Data Objects 2.7 Library

this is the DLL required for the ADODB objects


"Imran Ghani" <(E-Mail Removed)> wrote in message
news:65263DED-850C-4F2A-BE30-(E-Mail Removed)...
> Hi Patrick Molloy!
>
> I hope you have a good time during your holidays.
> I have written my code in excel according to your guidance, but its giving
> an error message of:
>
> user defined type not defined and highlighting the following text:
>
> Dim con As New ADODB.Connection
>
> Kindly guide me in this respect.
> Regards,
> Imran.
>
> "Patrick Molloy" wrote:
>
>> I'm out of the office for 2 days - so i'm afraid I cannot help until
>> Saturday. However, this code is excel vba and should be clear enough to
>> get
>> you going.
>> Open excel, go to the development environment (ALT+F11), add a module
>> (Insert/Module) then paste the code:
>> change the SQL statement and the name of the MS Access database
>> appropriately
>>
>> Sub LoadDataFromAccess()
>> Dim MyFile As String
>> Dim con As New ADODB.Connection
>> Dim rst As New ADODB.Recordset
>> Dim SQL As String
>> Dim i As Long
>>
>> MyFile = "Risk.mdb"
>> SQL = "SELECT * FROM BondTable"
>>
>> con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
>>
>> rst.Open SQL, con, adOpenStatic
>>
>> Cells.Clear
>>
>> For i = 0 To rst.Fields.Count - 1
>> Cells(1, i + 1).Value = rst.Fields(i).Name
>> Next
>>
>> Range("A2").CopyFromRecordset rst
>>
>> rst.Close
>> con.Close
>>
>> Set rst = Nothing
>> Set con = Nothing
>>
>> End Sub
>>
>>
>>
>> "Imran Ghani" <(E-Mail Removed)> wrote in message
>> news:C2F034F3-D9F0-41D7-837C-(E-Mail Removed)...
>> > Hi Patrick Molloy
>> > Thanks for your reply. Kindly do guide me about the whole process,
>> > mentioned
>> > by you, as I, being a novice, am certainly not much capable to handle
>> > the
>> > job
>> > independently. I'd much appreciate your helping hand.
>> > Regards,
>> > Imran.
>> >
>> > "Patrick Molloy" wrote:
>> >
>> >> ah. from your question I thought Excel was driving the import, but in
>> >> fact
>> >> MS Access is doing this..so AFAIK you can't format this way, you'd
>> >> have
>> >> to
>> >> write more VBA in Access to open the spreadsheet then format it. I
>> >> don't
>> >> think Access gives you much control.
>> >> What would be "better" is to instantiate excel, load the table from
>> >> the
>> >> query and then you'd have more control over the excel range.
>> >> Do you know how to do this?
>> >>
>> >>
>> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
>> >> news:7C27D37A-E9FC-4F84-89DA-(E-Mail Removed)...
>> >> > Hi Patrick Molloy
>> >> >
>> >> > I am exporting my data with a query as:
>> >> >
>> >> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
>> >> > "qryinvrgstr",
>> >> > "d:\invoice register.xls", True
>> >> >
>> >> > Its working fine and exporting the data ok, but I'd like also to get
>> >> > totals
>> >> > in the end of the data and also to format my whole data with lines
>> >> > all
>> >> > around. I'd much appreciate your guidance.
>> >> >
>> >> > Regards,
>> >> > Imran.
>> >> >
>> >> > "Patrick Molloy" wrote:
>> >> >
>> >> >> both can be done quite easily...but we'd need to see the code that
>> >> >> loads
>> >> >> the
>> >> >> sheet with the MS Access data to get a handle on the method used in
>> >> >> order
>> >> >> to
>> >> >> give you an appropriate response.
>> >> >>
>> >> >> for example you may have
>> >> >> Range("A1").CopyFromrecordset rst
>> >> >> or various other methods of loading the data to the sheet.
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Imran Ghani" <(E-Mail Removed)> wrote in
>> >> >> message
>> >> >> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
>> >> >> > Hi! I want to format my excel data that's been imported from MS
>> >> >> > Access,
>> >> >> > such
>> >> >> > that, ther's a box around all the data cells, also please help
>> >> >> > me,
>> >> >> > about
>> >> >> > how
>> >> >> > to have my aggregate fields at the end of the data. Can this be
>> >> >> > achieved
>> >> >> > using MS Excel 2007, automation using macros, or VBA. I'd much
>> >> >> > appreciate
>> >> >> > your guiding remarks. Thanks in advance.
>> >> >> > Regards,
>> >> >> > Imran.
>> >> >>
>> >> >>
>>

Hi Patrick Molloy!
I am sorry, I guessed wrong. Last time you wrote "I'm out of the office for
2 days" . So, I thought that way.
I am trying to run the code, but it is not accepting the database file path.
Kindly guide me how to write the valid path, for the code to access. I have
tried the full path and just the db file name also, but it is just giving the
error message of file not found.
Regards,
Imran.

"Patrick Molloy" wrote:

> " I hope you have a good time during your holidays. " ?? what holidays?
>
> In the development application go to Tools /References and check Microsoft
> Active Data Objects 2.7 Library
>
> this is the DLL required for the ADODB objects
>
>
> "Imran Ghani" <(E-Mail Removed)> wrote in message
> news:65263DED-850C-4F2A-BE30-(E-Mail Removed)...
> > Hi Patrick Molloy!
> >
> > I hope you have a good time during your holidays.
> > I have written my code in excel according to your guidance, but its giving
> > an error message of:
> >
> > user defined type not defined and highlighting the following text:
> >
> > Dim con As New ADODB.Connection
> >
> > Kindly guide me in this respect.
> > Regards,
> > Imran.
> >
> > "Patrick Molloy" wrote:
> >
> >> I'm out of the office for 2 days - so i'm afraid I cannot help until
> >> Saturday. However, this code is excel vba and should be clear enough to
> >> get
> >> you going.
> >> Open excel, go to the development environment (ALT+F11), add a module
> >> (Insert/Module) then paste the code:
> >> change the SQL statement and the name of the MS Access database
> >> appropriately
> >>
> >> Sub LoadDataFromAccess()
> >> Dim MyFile As String
> >> Dim con As New ADODB.Connection
> >> Dim rst As New ADODB.Recordset
> >> Dim SQL As String
> >> Dim i As Long
> >>
> >> MyFile = "Risk.mdb"
> >> SQL = "SELECT * FROM BondTable"
> >>
> >> con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
> >>
> >> rst.Open SQL, con, adOpenStatic
> >>
> >> Cells.Clear
> >>
> >> For i = 0 To rst.Fields.Count - 1
> >> Cells(1, i + 1).Value = rst.Fields(i).Name
> >> Next
> >>
> >> Range("A2").CopyFromRecordset rst
> >>
> >> rst.Close
> >> con.Close
> >>
> >> Set rst = Nothing
> >> Set con = Nothing
> >>
> >> End Sub
> >>
> >>
> >>
> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
> >> news:C2F034F3-D9F0-41D7-837C-(E-Mail Removed)...
> >> > Hi Patrick Molloy
> >> > Thanks for your reply. Kindly do guide me about the whole process,
> >> > mentioned
> >> > by you, as I, being a novice, am certainly not much capable to handle
> >> > the
> >> > job
> >> > independently. I'd much appreciate your helping hand.
> >> > Regards,
> >> > Imran.
> >> >
> >> > "Patrick Molloy" wrote:
> >> >
> >> >> ah. from your question I thought Excel was driving the import, but in
> >> >> fact
> >> >> MS Access is doing this..so AFAIK you can't format this way, you'd
> >> >> have
> >> >> to
> >> >> write more VBA in Access to open the spreadsheet then format it. I
> >> >> don't
> >> >> think Access gives you much control.
> >> >> What would be "better" is to instantiate excel, load the table from
> >> >> the
> >> >> query and then you'd have more control over the excel range.
> >> >> Do you know how to do this?
> >> >>
> >> >>
> >> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
> >> >> news:7C27D37A-E9FC-4F84-89DA-(E-Mail Removed)...
> >> >> > Hi Patrick Molloy
> >> >> >
> >> >> > I am exporting my data with a query as:
> >> >> >
> >> >> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> >> >> > "qryinvrgstr",
> >> >> > "d:\invoice register.xls", True
> >> >> >
> >> >> > Its working fine and exporting the data ok, but I'd like also to get
> >> >> > totals
> >> >> > in the end of the data and also to format my whole data with lines
> >> >> > all
> >> >> > around. I'd much appreciate your guidance.
> >> >> >
> >> >> > Regards,
> >> >> > Imran.
> >> >> >
> >> >> > "Patrick Molloy" wrote:
> >> >> >
> >> >> >> both can be done quite easily...but we'd need to see the code that
> >> >> >> loads
> >> >> >> the
> >> >> >> sheet with the MS Access data to get a handle on the method used in
> >> >> >> order
> >> >> >> to
> >> >> >> give you an appropriate response.
> >> >> >>
> >> >> >> for example you may have
> >> >> >> Range("A1").CopyFromrecordset rst
> >> >> >> or various other methods of loading the data to the sheet.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> "Imran Ghani" <(E-Mail Removed)> wrote in
> >> >> >> message
> >> >> >> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
> >> >> >> > Hi! I want to format my excel data that's been imported from MS
> >> >> >> > Access,
> >> >> >> > such
> >> >> >> > that, ther's a box around all the data cells, also please help
> >> >> >> > me,
> >> >> >> > about
> >> >> >> > how
> >> >> >> > to have my aggregate fields at the end of the data. Can this be
> >> >> >> > achieved
> >> >> >> > using MS Excel 2007, automation using macros, or VBA. I'd much
> >> >> >> > appreciate
> >> >> >> > your guiding remarks. Thanks in advance.
> >> >> >> > Regards,
> >> >> >> > Imran.
> >> >> >>
> >> >> >>
> >>
>

no worries

the file path should be precise

eg
MyFile = "S:\datafiles\testing\myAccessDatabaseName.mdb"

"Imran Ghani" <(E-Mail Removed)> wrote in message
news:E58E0F07-2B0C-4F4C-9B94-(E-Mail Removed)...
> Hi Patrick Molloy!
> I am sorry, I guessed wrong. Last time you wrote "I'm out of the office
> for
> 2 days" . So, I thought that way.
> I am trying to run the code, but it is not accepting the database file
> path.
> Kindly guide me how to write the valid path, for the code to access. I
> have
> tried the full path and just the db file name also, but it is just giving
> the
> error message of file not found.
> Regards,
> Imran.
>
> "Patrick Molloy" wrote:
>
>> " I hope you have a good time during your holidays. " ?? what holidays?
>>
>> In the development application go to Tools /References and check
>> Microsoft
>> Active Data Objects 2.7 Library
>>
>> this is the DLL required for the ADODB objects
>>
>>
>> "Imran Ghani" <(E-Mail Removed)> wrote in message
>> news:65263DED-850C-4F2A-BE30-(E-Mail Removed)...
>> > Hi Patrick Molloy!
>> >
>> > I hope you have a good time during your holidays.
>> > I have written my code in excel according to your guidance, but its
>> > giving
>> > an error message of:
>> >
>> > user defined type not defined and highlighting the following text:
>> >
>> > Dim con As New ADODB.Connection
>> >
>> > Kindly guide me in this respect.
>> > Regards,
>> > Imran.
>> >
>> > "Patrick Molloy" wrote:
>> >
>> >> I'm out of the office for 2 days - so i'm afraid I cannot help until
>> >> Saturday. However, this code is excel vba and should be clear enough
>> >> to
>> >> get
>> >> you going.
>> >> Open excel, go to the development environment (ALT+F11), add a module
>> >> (Insert/Module) then paste the code:
>> >> change the SQL statement and the name of the MS Access database
>> >> appropriately
>> >>
>> >> Sub LoadDataFromAccess()
>> >> Dim MyFile As String
>> >> Dim con As New ADODB.Connection
>> >> Dim rst As New ADODB.Recordset
>> >> Dim SQL As String
>> >> Dim i As Long
>> >>
>> >> MyFile = "Risk.mdb"
>> >> SQL = "SELECT * FROM BondTable"
>> >>
>> >> con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
>> >>
>> >> rst.Open SQL, con, adOpenStatic
>> >>
>> >> Cells.Clear
>> >>
>> >> For i = 0 To rst.Fields.Count - 1
>> >> Cells(1, i + 1).Value = rst.Fields(i).Name
>> >> Next
>> >>
>> >> Range("A2").CopyFromRecordset rst
>> >>
>> >> rst.Close
>> >> con.Close
>> >>
>> >> Set rst = Nothing
>> >> Set con = Nothing
>> >>
>> >> End Sub
>> >>
>> >>
>> >>
>> >> "Imran Ghani" <(E-Mail Removed)> wrote in message
>> >> news:C2F034F3-D9F0-41D7-837C-(E-Mail Removed)...
>> >> > Hi Patrick Molloy
>> >> > Thanks for your reply. Kindly do guide me about the whole process,
>> >> > mentioned
>> >> > by you, as I, being a novice, am certainly not much capable to
>> >> > handle
>> >> > the
>> >> > job
>> >> > independently. I'd much appreciate your helping hand.
>> >> > Regards,
>> >> > Imran.
>> >> >
>> >> > "Patrick Molloy" wrote:
>> >> >
>> >> >> ah. from your question I thought Excel was driving the import, but
>> >> >> in
>> >> >> fact
>> >> >> MS Access is doing this..so AFAIK you can't format this way, you'd
>> >> >> have
>> >> >> to
>> >> >> write more VBA in Access to open the spreadsheet then format it. I
>> >> >> don't
>> >> >> think Access gives you much control.
>> >> >> What would be "better" is to instantiate excel, load the table from
>> >> >> the
>> >> >> query and then you'd have more control over the excel range.
>> >> >> Do you know how to do this?
>> >> >>
>> >> >>
>> >> >> "Imran Ghani" <(E-Mail Removed)> wrote in
>> >> >> message
>> >> >> news:7C27D37A-E9FC-4F84-89DA-(E-Mail Removed)...
>> >> >> > Hi Patrick Molloy
>> >> >> >
>> >> >> > I am exporting my data with a query as:
>> >> >> >
>> >> >> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
>> >> >> > "qryinvrgstr",
>> >> >> > "d:\invoice register.xls", True
>> >> >> >
>> >> >> > Its working fine and exporting the data ok, but I'd like also to
>> >> >> > get
>> >> >> > totals
>> >> >> > in the end of the data and also to format my whole data with
>> >> >> > lines
>> >> >> > all
>> >> >> > around. I'd much appreciate your guidance.
>> >> >> >
>> >> >> > Regards,
>> >> >> > Imran.
>> >> >> >
>> >> >> > "Patrick Molloy" wrote:
>> >> >> >
>> >> >> >> both can be done quite easily...but we'd need to see the code
>> >> >> >> that
>> >> >> >> loads
>> >> >> >> the
>> >> >> >> sheet with the MS Access data to get a handle on the method used
>> >> >> >> in
>> >> >> >> order
>> >> >> >> to
>> >> >> >> give you an appropriate response.
>> >> >> >>
>> >> >> >> for example you may have
>> >> >> >> Range("A1").CopyFromrecordset rst
>> >> >> >> or various other methods of loading the data to the sheet.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> "Imran Ghani" <(E-Mail Removed)> wrote in
>> >> >> >> message
>> >> >> >> news:529968BE-BF97-480E-BF8C-(E-Mail Removed)...
>> >> >> >> > Hi! I want to format my excel data that's been imported from
>> >> >> >> > MS
>> >> >> >> > Access,
>> >> >> >> > such
>> >> >> >> > that, ther's a box around all the data cells, also please help
>> >> >> >> > me,
>> >> >> >> > about
>> >> >> >> > how
>> >> >> >